Stored Procedure Range Not Working

[red vertigo]

Hi All,
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
PO_NUMBER_POMSTR,
VENDOR_NUMBER_POMSTR,
SHIP_NUMBER_POMSTR,
CHANGE_ORDER_POMSTR,
FOB_POINT_POMSTR,
ROUTING_POMSTR,
DATE_ISSUED_POMSTR,
DATE_LAST_RECPT_POMSTR,
CONTACT_PERSON_1_POMSTR,
PREPAID_COLLECT_POMSTR,
TERMS_POMSTR,
AMOUNT_ESTIMATED_POMSTR,
AMOUNT_RECEIVED_POMSTR,
AMOUNT_PAID_POMSTR,
LOCATION_CODE_POMSTR,
SHIPPING_POINT_POMSTR,
PRINT_IND_POMSTR,
BUYER_POMSTR,
SHIPMENT_POMSTR,
STATUS_POMSTR,
CURRENCY_POMSTR,
CURRENCY_STATUS_POMSTR,
AMOUNT_EST_CUR_POMSTR,
AMOUNT_REC_CUR_POMSTR,
AMOUNT_PAID_CUR_POMSTR, --Finish tbl_IPPOMST
IPPOITM_SID, --Start tbl_IPPOITM
PO_NUMBER_POITEM,
ITEM_NUMBER_POITEM,
CATEGORY_POITEM,
DESCRIPTION_POITEM,
VENDOR_NUMBER_POITEM,
DATE_ORIGINAL,
DATE_RESCHEDULED,
ACCOUNT_NUMBER_POITEM,
STOCK_NUMBER_POITEM,
JOB_NUMBER_POITEM,
RELEASE_WO_POITEM,
QUANTITY_ORDERED_POITEM,
QUANTITY_RECVD_POITEM,
UOM_POITEM,
UNIT_WEIGHT_POITEM,
UNIT_COST_POITEM,
EXTENDED_TOTAL_POITEM,
MATERIAL_NUMBER_POITEM,
COMPLETE_POITEM,
LOCATION_CODE_POITEM,
INSPECTION_POITEM,
BOM_ITEM_POITEM,
COST_ACCOUNT_POITEM,
CHANGE_ORDER_POITEM,
TAX_CODE_POITEM,
ISSUE_CODE_POITEM,
QUANTITY_INSPECT_POITEM,
EXC_RATE_CURR_POITEM,
UNIT_COST_CURR_POITEM,
EXTENDED_TOTAL_CURR_POITEM,
PLANNER_POITEM,
BUYER_POITEM --Finish tbl_IPPOITM
IPVENDM_SID, --Start tbl_IPVENDM
VENDOR_NUMBER_VENMSTR,
VENDOR_NAME_VENMSTR,
ADDRESS_LINE_1_VENMSTR,
ADDRESS_LINE_2_VENMSTR,
ADDRESS_LINE_3_VENMSTR,
CITY_VENMSTR,
STATE_VENMSTR,
ZIP_CODE_VENMSTR,
COUNTRY_VENMSTR --Finish tbl_IPVENDM

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?

Any help would be greatly appreciated.

Many Thanks*

rohan

* & Merry Christmas!

[Erland Sommarskog]

red vertigo (ro***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[red vertigo]

Erland Sommarskog wrote:

red vertigo (ro***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland,
Thankyou very much for replying to my post, for your insight and
suggestions. I will make changes and see if I can get this to work.

rohan:)