Need SQL Query Help, matching a stored procedure l

问题:

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?

回答1:

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?

回答2:

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

回答3:

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)

回答4:

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)

回答5:

** 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.

回答6:

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