Passing a list of columns to a stored procedure

Question

ok, here is my situation, I have a list of variables that I want to pass to the stored procedure. each variable is a column in the database (dbImage) and has a boolean value.

--Columnslist contains Var1, Var2, Var3 @ColumnsList varchar(Max) 

I want to pass them to the select statement and var1, var2 and var3 as true

 SELECT @ColumnsList from dtImage WHERE @ColumnsList = True 

I know this can be confusing, I know what I want to do. It’s just very difficult for me to explain what I want to do.

Answer 1

You mean, suppose you have a table as follows

 CREATE TABLE bits ( id INT PRIMARY KEY, col1 BIT, col2 BIT, col3 BIT, col4 BIT ) 

Populated as follows

 INSERT INTO bits VALUES (1,0,0,0,0), (2,1,1,1,1), (3,1,1,0,0) 

And you pass the string

 DECLARE @ColumnsList VARCHAR(MAX) = 'col1,col2,col3' 

Do you want to return row 2 because this is the only value where the value for all of these columns is 1 ?

If possible, then the most reasonable solution would be dynamic SQL or some bitwise query. At the same time, there will be no solution.

 SELECT id FROM bits UNPIVOT(val FOR col IN (col1, col2, col3, col4)) unpvt JOIN (SELECT col FROM (SELECT CAST('<c>' + REPLACE(@ColumnsList, ',', '</c><c>') + '</c>' AS XML) AS x) x CROSS APPLY (SELECT t.split.value('.', 'sysname') AS col FROM x.nodes('/c') t(split)) ca) cols ON cols.col = unpvt.col GROUP BY id HAVING COUNT(CASE WHEN val = 0 THEN 1 END) = 0 

Answer 2

You can create a dynamic sql statement and execute it with sp_ExecuteSql
http://msdn.microsoft.com/en-us/library/ms188001.aspx