Sql-server – How to split the comma separated list in the stored procedure

Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.

I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works on 2005). The same strategy would work better for you in this situation but you may need to adapt the implementation based on the fact you are using the Entity Framework.

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

EDIT

As you rightly pointed out I am wrong about the fact this works on 2005 - sorry about that!

However, I have some alternate suggestions.

As SQL Server 2005 does support table variables (just not as parameters to stored procedures as you pointed out) you could parse the delimited string and insert the id's into a table variable. You could then use the table variable to perform a set-based update.

Alternatively the link below provides a different take on the same problem by persisting the values to a table first thereby avoiding serialization and de-serialization of the id values:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx

I hope this helps you.

Well you could do something like this which would avoid the cursor that you had in the query that you originally posted. Firstly, change the definition of your columns table so it looks like this:

DECLARE @columns TABLE
(
    ID INT IDENTITY NOT NULL,
    NAME NVARCHAR(128) NOT NULL
);

Then, using whatever method you have; split the string and populate the table above. After which you could do something like this to get your desired results:

EDIT: Changed the body of the loop based on your comment below.

CREATE TABLE ##results
(
    RecordID INT NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL
);

DECLARE @i INT = 1;
DECLARE @j INT = (SELECT MAX(ID) FROM @columns);
DECLARE @c NVARCHAR(128); -- used to store column name
DECLARE @s NVARCHAR(max); -- to store the command....

WHILE(@i <= @j)
BEGIN
SET @c = (SELECT NAME FROM @columns WHERE ID = @i);

IF @i > 1
    SET @s += 'OR (' + @c + ' IS NULL) ';
ELSE
    SET @s += '(' + @c + ' IS NULL) ';

SET @s = 'INSERT ##results SELECT RecordID, ''' 
       + @c + ''' FROM some.table WHERE ' 
       + @c + ' IS NULL; '
@i += 1;
END
EXECUTE (@s);

SELECT * FROM ##results;
DROP TABLE ##results;

Be aware though that depending on the amount of rows you have in your table this could take a while...

I hope this helps you.