需要 SQL 查询帮助，将存储过程列表参数与各个列匹配 [英] Need SQL Query Help, matching a stored procedure list parameter against individual columns

问题描述

我有一个存储过程..

I have a stored procedure..

Proc:
spMyStoredProcedure

需要一个参数:

@List varchar(255)

@List 将是一个逗号分隔的值列表，即...@List = '1,2,3'
(清晰度……单个值 1 表示 col1 = true 的所有记录)

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)

我有一个包含以下列的表:ID int、col1 位、col2 位、col3 位, col4 位.

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

我希望我的结果只包含列表中那些行的 ID.即 12、13、14.

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

我的第一个想法是遍历列表并进行选择.IE.对于第一个值为 1 的值，我抓取 col1 中为 1(真)的所有记录(导致记录 12).然后转到第二个值为 2 的值，并在 col2 中获取所有记录为 1(真)(导致记录 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?

推荐答案

我认为这解决了问题:

declare @sql as nvarchar(max)

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

这是假设列表不是用户生成的，而是代码生成的，所以我不防范 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.

这是您要找的吗?

这篇关于需要 SQL 查询帮助，将存储过程列表参数与各个列匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！