SEO

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!

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog wrote:
> 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/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