Please Proofread This SP 2

[puitar]

Greets,

I'm new to stored procedures. My ASP page collects query info eg field names to query, and query strings.

My stored procedure reads...

ALTER Procedure inetPasysList
(
@pAssetNo varchar(40) =null,
@pDescription varchar (40) =null,
@pBrand varchar (40) =null,
@pModel varchar (40) =null,
@pSerialNo varchar (40) =null,
@pAcquisitionDate datetime =null,
@pValueAtAcqusition int =null,
@pPurchaseOrderNo varchar (50) =null,
@pSupplier varchar (35) =null,
@pSupplierInvoiceNo varchar (10) =null,
@pLocation varchar (6) =null,
@pResponsibleOfficer varchar (40) =null,
@pCostCode varchar (6) =null,
@pCategory varchar (40) =null,
@pTransferTo varchar (10) =null,
@pTransferFrom varchar (10) =null,
@pDisposalDate DateTime =null,
@pMethodOfDisposal varchar (10) =null,
@pCorrectionalCentre varchar (50) =null,
@pActive bit =null
)
As
SELECT *
FROM pasys
WHERE AssetNo like isnull(@pAssetNo, AssetNo)
and Description like isnull(@pDescription, Description)
and Brand like isnull(@pBrand, Brand)
and Model like isnull(@pModel, Model)
and SerialNo like isnull(@pSerialNo, SerialNo)
and AcquisitionDate like isnull(@pAcquisitionDate, AcquisitionDate)
and ValueAtAcqusition like isnull(@pValueAtAcqusition, ValueAtAcqusition)
and PurchaseOrderNo like isnull(@pPurchaseOrderNo, PurchaseOrderNo)
and Supplier like isnull(@pSupplier, Supplier)
and SupplierInvoiceNo like isnull(@pSupplierInvoiceNo, SupplierInvoiceNo)
and Location like isnull(@pLocation, Location)
and ResponsibleOfficer like isnull(@pResponsibleOfficer, ResponsibleOfficer)
and CostCode like isnull(@pCostCode, CostCode)
and Category like isnull(@pCategory, Category)
and TransferTo like isnull(@pTransferTo, TransferTo)
and TransferFrom like isnull(@pTransferFrom, TransferFrom)
and DisposalDate like isnull(@pDisposalDate, DisposalDate)
and MethodOfDisposal like isnull(@pMethodOfDisposal, MethodOfDisposal)
and CorrectionalCentre like isnull(@pCorrectionalCentre, CorrectionalCentre)
and Active like isnull(@pActive, Active)

mmm... heres my asp code

set tDConnection = server.createObject("ADODB.Connection&quot
tDConnection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=servername;UID=userid;PWD=password;DATABASE=pasys"
Set cmd = Server.CreateObject("ADODB.Command&quot
Set cmd.ActiveConnection = tDConnection
cmd.CommandText = "inetPasysList" ' name of sp
cmd.CommandType = adCmdStoredProc


' Ask the server about the parameters for the stored proc
cmd.Parameters.Refresh

tP1 = "@p" & tField1_Name
if tField1_Value <> &quot;&quot; then
cmd.Parameters(tP1) = tField1_Value
end if

tP2 = &quot;@p&quot; & tField2_Name
if tField2_Value <> &quot;&quot; then
cmd.Parameters(tP2) = tField2_Value
end if
Set xrs = cmd.Execute


should this work?

p

 

[chiph]

You'll have to change it from ALTER to CREATE for your initial installation, though.

The thing I'm really wondering about is all those &quot;like&quot; operators.

and Supplier like isnull(@pSupplier, Supplier)

So if you leave off the Supplier parameter when you call it, the initializer will set @pSupplier to NULL. The ISNULL() function checks the @pSupplier to see if it's NULL (and it is), so uses the replacement parameter (Supplier in this case). So your condition then becomes:

and Supplier like Supplier

The LIKE operator wants a '%' to do matching on. It's OK if it's missing, but should the caller provide a non-null parameter &quot;ACME&quot;, you get:

and Supplier like ACME

And it gets confused since there's no single-quote around the value. Even if you supply the quotes, it'll only match suppliers named 'ACME', and won't match on 'ACME Plumbing', which is what I think you wanted.

Chip H.

 

[tlbroadbent]

The SP looks good for the most part. I have the following recommendations.

Only use the LIKE predicate on character type columns (char, varchar, nchar, etc.) when you want the user to search by a partial entry such as the first 3 letters of a name. If the user must enter the entire name use the '=' criteria instead of LIKE.

Add + '%' on the end of each criteria statement if partial string searches are allowed. This will find strings at the start of the column.

And Description like isnull(@pDescription, Description) + '%'


If you want the search to find an embedded string, no matter where it is located in the column, add '%' + before the also.

And Description like '%' + isnull(@pDescription, Description) + '%'

Don't use LIKE to search numeric, bit or datetime columns, unless the columns are converted to character types. However, converting these data types to character types usually results in erroneous selections. Use =, <, >, <> criteria or range searches.

and ValueAtAcqusition like isnull(@pValueAtAcqusition, ValueAtAcqusition)
and DisposalDate = isnull(@pDisposalDate, DisposalDate) Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[puitar]

Thanks guys!

Should I change the where statement to something like
...and Description like isnull (@pDescription,'%')...

If the parameter isnull then insert wildcard '%'??????

I want the procedure to return ALL records if no parameters are given/sent from browser.

My ASP code will add '%' around the query string/parameter if needed.

Do you know of any good sites that might help me on my quest to master ASP+SP?

p