Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 StoredProcedure List Parameter

Author  Topic 

jp2code
Posting Yak Master

175 Posts

Posted - 2008-10-13 : 13:46:26
Say I have a basic stored procedure like so:
create procedure sp_GetParts(@Date1 DateTime, @MODEL varchar(50)) as
SELECT PartNo, SerialNo, MfgDate
FROM dbo.Parts
WHERE (MfgDate=@Date1) AND (PartNo=@MODEL)
I have several basic procedures like this stored on our SQL 2000 Server now, and they return all of these parts.

What management has asked is that they allow us to give them a list of SerialNo values that fit this category to further narrow the resulting dataset.

The problem is: There could be any number of SerialNo values!

How could I modify my Stored Procedure so that they could take a List parameter?
create procedure sp_GetPartsFiltered(@Date1 DateTime, @MODEL varchar(50), 
"List" @SnList varchar(20)) as
SELECT PartNo, SerialNo, MfgDate
FROM dbo.Parts
WHERE (MfgDate=@Date1) AND (PartNo=@MODEL) AND SerialNo IN @SnList
I know the code above does not work, but hopefully this will get my point across.


Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 13:49:22
[code]create procedure sp_GetPartsFiltered(@Date1 DateTime, @MODEL varchar(50),
@SnList varchar(1000)) as
SELECT PartNo, SerialNo, MfgDate
FROM dbo.Parts
WHERE (MfgDate=@Date1) AND (PartNo=@MODEL)
AND ','+ @SnList+',' LIKE '%,' + CAST(SerialNo AS varchar(10)) + ',%' [/code]
Go to Top of Page

jp2code
Posting Yak Master

175 Posts

Posted - 2008-10-13 : 17:58:46
Thank you!


Avoid Sears Home Improvement
Go to Top of Page
   

- Advertisement -