Should table parameters be used here?

Question

I have the following query:

@"UPDATE students SET IsDeleted = 1 WHERE StudentId IN ( SELECT StudentId FROM Class where PassId IN ( SELECT Id FROM ClassValueTable WHERE IsDeleted <> 1" + activeIds)))"; 

where activeIds = a string of some numbers, for example: 1,2,3,4 ...

I want to convert this request to a stored procedure. My question is, what is the best way to pass activeIds as a parameter to this stored procedure?

* The code is in C # and I am using SQL Server 2008

Answer 1

Yes, use table options. First create a type:

 CREATE TYPE dbo.StudentIDs(ID INT PRIMARY KEY); 

Now your procedure can use this (note that I changed your nested IN queries to the correct connection):

 CREATE PROCEDURE dbo.MarkStudentsAsDeleted @IDs dbo.StudentIDs READONLY AS BEGIN SET NOCOUNT ON; UPDATE s SET IsDeleted = 1 FROM dbo.Students AS s INNER JOIN dbo.Class AS c ON s.StudentId = c.StudentId INNER JOIN dbo.ClassValueTable AS ct ON c.PassId = ct.Id WHERE ct.IsDeleted <> 1 AND EXISTS (SELECT 1 FROM @IDs WHERE StudentID = s.StudentID); END GO 

And your C # code will be passed to a DataTable or, nevertheless, you have compiled a collection of activeIds, not a comma-separated list.

 DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(int)); dt.Rows.Add(1); dt.Rows.Add(2); dt.Rows.Add(3); dt.Rows.Add(4); ... open connection etc. ... SqlCommand cmd = new SqlCommand("dbo.MarkStudentsAsDeleted", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter tvp1 = cmd.Parameters.AddWithValue("@IDs", dt); tvp1.SqlDbType = SqlDbType.Structured; cmd.ExecuteNonQuery(); ... close, dispose, etc. ... 

If you want to insist on passing the string to the stored procedure, you will need to use the split function. For example:

 CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) = ',' ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT [value] = yivalue('(./text())[1]', 'int') FROM ( SELECT x = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.') ) AS a CROSS APPLY x.nodes('i') AS y(i) ); GO 

Now your stored procedure could be:

 CREATE PROCEDURE dbo.MarkStudentsAsDeleted @IDs VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; UPDATE s SET IsDeleted = 1 FROM dbo.Students AS s INNER JOIN dbo.Class AS c ON s.StudentId = c.StudentId INNER JOIN dbo.ClassValueTable AS ct ON c.PassId = ct.Id WHERE ct.IsDeleted <> 1 AND EXISTS (SELECT 1 FROM dbo.SplitInts(@IDs, ',') WHERE Item = s.StudentID); END GO