CODEWITHSUNDEEP
sql-server-2008stored-procedures
Joel R
Joel R

Reputation: 79

passing a list of columns into a stored procedure

ok, here's my situation, I have a list of variables I want to pass into a stored procedure. each variable represents a column in a database(dbImage) and are boolean.

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

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

SELECT @ColumnsList from dtImage WHERE @ColumnsList = True

I know it may be confusing, I know what I want to do. I just have a really hard time explaining what I want to do.

Upvotes: 4

Views: 1492

Answers (2)

Martin Smith
Martin Smith

Reputation: 452977

Do you mean that 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 in the string 

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

You want to get back row 2 because that's the only one where the value for all these columns is 1?

If so probably dynamic SQL or some sort of bitwise query will be the most sensible solution. In the meantime here's a solution without either.

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

Upvotes: 1

Cosmin
Cosmin

Reputation: 2385

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

Upvotes: 0

Related Questions