Stored procedure for selecting multiple records

Question

I need to select records say 2000 from a table with a matching timestamp from c# .net code. Eg:

SELECT * 
FROM ITEMDATA_TABLE 
WHERE ITEMNAME='Item1' and TimeStamp='2010-04-26 17:15:05.667'

The above query has to execute for 2000 items more for the same timestamp.

for this we can use

SELECT * 
FROM ITEMDATA_TABLE 
WHERE ITEMNAME in ('Item1','Item2','Item3','Item4',......) 
  AND TimeStamp='2010-04-26 17:15:05.667'

from SQL Server Management Studio.

I have tried appending all Item names to one string variable and giving it as a parameter to the stored procedure,but it resulted into a wrong concatenation.

How can I do this as a stored procedure executing from the .net code? Can anyone suggest me/correct me in doing this?

Thanks & Regards

Padma

Answer 1

not to tell you to do it another way, but wouldn't this be easier if you selected all items with the timestamp you're looking for and then iterating this record set in your c# code?

Answer 2

If doing it from a stored procedure, I would create a temp table of all valid item names, insert into that (ie: just a single table), and do a query against that as the primary, joined to the master where IDs / DateTime qualified. Then delete the temp table.

Answer 3

If you're on SQL Server 2008 (you didn't mention your version), you can use table-valued parameters.

If you're on SQL Server 2005 or below, check out Erland Sommarskog's excellent articles on how to deal with lists of parameters in stored procedures.

You cannot just pass those in as a string - that does not work.

Answer 4

Following may be the code you are looking for :

SET @SQLString = N'SELECT *
    FROM table1
    WHERE timet = @time and items in (@item)';


DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@time timestamp,
    @item varchar(max) ';


EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@time = '2010-04-26 17:15:05.667'
    ,@item = '''Item1'',''Item2'',''Item3'',''Item4'''
    ;

Answer 5

In SQL 2005 you can pass the list of item as XML parameter to the stored procedure and then write a query against the xml parameter. something like this

SELECT ItemData_Table.* FROM ItemData_Table CROSS APPLY MyxmlParameter.nodes('item') data(i) WHERE ItemData_Table.ItemId = i.value('@itemid', INT)

Here MyxmlParameter is the XML you passed from you .NET code to the stored procedure.