Posted by red vertigo on 12/22/06 09:33

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!

[Back to original message]