|
|
Posted by red vertigo on 12/22/06 12:24
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/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/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:)
Navigation:
[Reply to this message]
|