Posted by Erland Sommarskog on 12/22/06 11:33

red vertigo (rohankinsella@hotmail.com) writes:
> I am trying to write a basic stored procedure to return a range of
> values but admit that I am stumped. The procedure syntax used is:
>
> ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(
>
> @StartPoNumber PONumberType = 'Null',
> @FinishPoNumber PONumberType = 'Null')
>
> AS
>
> SET @StartPoNumber = 'PO_NUMBER_POMSTR'
> SET @FinishPoNumber = 'PO_NUMBER_POMSTR'
>
> SELECT IPPOMST_SID, --Start tbl_IPPOMST
>...
> FROM tbl_IPPOMST
> JOIN tbl_IPPOITM
> ON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTR
> JOIN tbl_IPVENDM
> on tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTR
> WHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber AND
> tbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumber
>
> Basically, no rows are returned for the valid (records in database)
> range I enter. I have been troubleshopoting the syntax. This has
> involved commenting out references to @FinishPoNumber so in effect I
> just pass in a valid PO Number using @StartPoNumber parameter. This
> works in terms of returning all 76545 PO records.
>
> Can anyone help me to identify why this syntax will not return a range
> of PO records that fall between @StartPoNumber and @FinishPoNumber?

With the sample code posted, the procedure only returns data if there any
rows with the value PO_NUMBER_POMSTR. Are there any such rows?

If you comment out the initial SET statements, and call the procedure
like this:

EXEC Rpt_LegacyPurchaseOrderSp @StartPoNumber = 'Putte'

you will not get any rows back, because @FinnishPoNumber is the string
'Null' which is before Putte.

If you change the procedure like this, it may work better:

ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(

@StartPoNumber PONumberType = NULL,
@FinishPoNumber PONumberType = NULL) -- No quotes around NULL

-- Remove SET statements

SELECT ...
...
WHERE tbl_IPPOMST.PO_NUMBER_POMSTR >=
isnull(@StartPoNumber, SELECT MIN(PO_NUMBER_POMSTR)
FROM tbl_IPPOMST)
AND tbl_IPPOMST.PO_NUMBER_POMSTR <=
isnull(@FinishPoNumber, SELECT MAX(PO_NUMBER_POMSTR)
FROM tbl_IPPOMST)

Normally, you should use coalesce to replace NULL with another value, but
in this special case isnull() may be better of performance reasons.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]