views:

734

answers:

6

I have a stored procedure..

Proc:
spMyStoredProcedure

Takes a Param:

@List varchar(255)

The @List would be a comma separated list of values i.e... @List = '1,2,3'
(clarity.. a single value of 1 would mean all records with col1 = true)

I have a table with these columns: ID int, col1 bit, col2 bit, col3 bit, col4 bit.

ID | col1 | col2 | col3 | col4
------------------------------
12 |   0  |   1  |   0  |  0
13 |   1  |   0  |   0  |  0
14 |   0  |   0  |   1  |  0
15 |   0  |   0  |   0  |  1

I'd like my result to only include ID's for those rows in the list. i.e. 12,13,14.

My first thought is to loop through the list and do a select. ie. for the first value being 1, I grab all records with a 1 (true) in col1 (resulting in record 12). Then move onto the second value being 2 and grab all records with a 1 (true) in col2 (resulting in record 14) and so on.. I'm wondering if there's a more efficient/better/cleaner way to do this?

+2  A: 

I think this solves the problem:

declare @sql as nvarchar(max)

set @sql = 'select * from table where col' + 
           replace(@list, ',', '=1 or col') + '=1'
sp_executesql @sql

This is assuming that list is not user-generated, and it's code generated, so I'm not guarding against SQL injection attacks. A list like this isn't generally something that's user-generated, so that's why I'm assuming as such.

Is this what you're looking for?

Eric
+1 Clever use of replace(). This is much better than my own solution above.
Scott Anderson
+1 to correct the sql injection police ;-)
Andomar
A: 

** wrong answer, check Eric's answer for a better reply!

Luckily, your list seems to match the IN format:

 select * from yourtable where id in (1,2,3,4)

So you could easily build a dynamic query:

declare @query varchar(4000)
set @query = 'select * from yourtable where id in (' +
    @list + ')'
 exec sp_executesql @query

Keep in mind that your client has to ensure the @list parameter is injection clean. You could verify in the stored procedure that it doesn't contain any single quotes.

Andomar
-1 again (sorry some one else posted a question and deleted it)..this opens you up to sql injection..
Miyagi Coder
I'm going to disagree Myagi. If you aren't cleaning data before it ends up in your stored proc, you're doing it wrong. T-SQL shouldn't be used to prevent injection.
Scott Anderson
@Scott...Parameterized stored procs used properly will prevent SQL injection...
Miyagi Coder
Many applications use dynamic SQL without being vulnerable to injection (I'm sure stackoverflow is one of them.)
Andomar
@Andomar - I dont disagree with you about using dynamic sql...but this particular case opens you up for Sql Injection...
Miyagi Coder
No they won't in the case of dynamic sql. I suggest you read up on that. Even when parameterized, when using a parameter to feed directly into *dynamic* sql can result in arbitrary code being executed. Regardless, in this case, I think it's silly to mod down for sql injection that won't happen as long as you trust where the list is coming from.
Scott Anderson
Parameterizing does not protect against SQL injection with dynamic queries. In this example, @list could contain ';drop table user;--, wether the sp was parameterized or not...
Andomar
@Scott Show me an any article and or documentation that says that this a best practice and i will read up on it...
Miyagi Coder
@Andomar Thats why i said "Parameterized stored procs used properly"..in this case you are not using a parameter properly.
Miyagi Coder
@Miyagi I was simply correcting you when you said that parameterized queries protected you against sql injection by default. This is not correct. Dynamic SQL needs extra protections. Those protections should come from internal business rules, not T-SQL. Hence voting him down was a bit harsh.
Scott Anderson
@Scott I never said that parameterized stored procs will protect against Sql injection by default. "Parameterized stored procs used properly..." There is a big difference in using a stored proc properly and using the answer posted above. I believe the downvote relects that...
Miyagi Coder
A: 

If you are using Sql Server 2005 you should be able to use the PIVOT function to transpose your data.

Miyagi Coder
A: 

Consider the following, using dynamic SQL. This is probably not the best method, but it's the only way I can think of to not issue multiple select statements against your table. This assumes you have cleansed your list of values and you've done the error checking first...

CREATE TABLE #tmp
(
    ID INT,
    col1 BIT,
    col2 BIT,
    col3 BIT,
    col4 BIT
)
INSERT INTO #tmp (ID, col1, col2, col3, col4) VALUES (12,0,1,0,0)
INSERT INTO #tmp (ID, col1, col2, col3, col4) VALUES (13,1,0,0,0)
INSERT INTO #tmp (ID, col1, col2, col3, col4) VALUES (14,0,0,1,0)
INSERT INTO #tmp (ID, col1, col2, col3, col4) VALUES (15,0,0,0,1)

DECLARE @List VARCHAR(255)
SELECT @List = '1,2,3'

-- create dynamic sql statement
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT ID FROM #tmp WHERE '

-- append where statement
IF CHARINDEX('1',@List) > 0 BEGIN
    SET @sql = @sql + 'col1 = 1 OR '
END
IF CHARINDEX('2',@List) > 0 BEGIN
    SET @sql = @sql + 'col2 = 1 OR '
END
IF CHARINDEX('3',@List) > 0 BEGIN
    SET @sql = @sql + 'col3 = 1 OR '
END
IF CHARINDEX('4',@List) > 0 BEGIN
    SET @sql = @sql + 'col4 = 1 OR '
END

-- remove the trailing 'OR' and execute
SET @sql = SUBSTRING(@sql,0,LEN(@sql)-2)
EXEC (@sql)
Scott Anderson
A: 

Usually I use a calculated column to store a total of bit columns. Assuming you cannot define the calculated column, we can use similar approach like this

SELECT ID FROM SampleTable WHERE (col1*1 + col2*2 + col3*4 + col4*8) IN (1, 2, 3)

Since 1,2,3 are all concatenated in a variable, for that I usually use a User Defined function as defined at http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select.

So, it would be something like

SELECT ID FROM SampleTable WHERE (col1*1 + col2*2 + col3*4 + col4*8) IN dbo.CSVToInt(@List)
But col3*4 does not equal 3?
Andomar
A: 

We use a UDF that parses a comma-list and returns a table value:

CREATE FUNCTION [dbo].[ListToTable] (
  @list VARCHAR(MAX),
  @separator VARCHAR(MAX) = ','
)
RETURNS @table TABLE (Value VARCHAR(MAX))
AS BEGIN
   DECLARE @position INT, @previous INT
   SET @list = @list + @separator
   SET @previous = 1
   SET @position = CHARINDEX(@separator, @list)
   WHILE @position > 0 BEGIN
      IF @position - @previous > 0
         INSERT INTO @table VALUES (LTRIM(RTRIM(SUBSTRING(@list, @previous, @position - @previous))))
      IF @position >= LEN(@list) BREAK
      SET @previous = @position + 1
      SET @position = CHARINDEX(@separator, @list, @previous)
   END
   RETURN
END

Then you can do something like:

SELECT *
FROM MyTable m
JOIN dbo.ListToTable(@List) l
  ON m.ID = l.Value

The function above also takes an optional separator like so:

SELECT *
FROM MyTable m
JOIN dbo.ListToTable('1:2:3', ':') l
  ON m.ID = l.Value
Jason DeFontes