从逗号连接的列表创建SQL表 [英] Creating a SQL table from a comma concatenated list

问题描述

我正在运行SQL Server，并且有一个存储过程.我想用WHERE IN子句做一条选择语句.我不知道列表会持续多久，所以我尝试了以下方法

I am running SQL Server and I have a stored procedure. I want do a select statement with a WHERE IN clause. I don't know how long the list will be so right now I have tried something as follows

SELECT * FROM table1 WHERE id IN (@idList)

在此解决方案中的

@idList是varChar(max).但这是行不通的.我听说过要传递表值，但是我对如何做到这一点感到困惑.任何帮助都很好

in this solution @idList is a varChar(max). but this doesn't work. I heard about passing in table values, but I am confused about how to do that. Any help would be great

推荐答案

我建议使用一个函数来拆分传入列表(使用链接(马丁发表评论).

I would suggest using a function to split the incoming list (use the link that Martin put in his comment).

将split函数的结果存储在临时表或表变量中，并将其联接到查询中，而不是WHERE子句中

Store the results of the split function in a temporary table or table variable and join it in your query instead of the WHERE clause

select * into #ids from dbo.Split(',', @idList)

select t.*
from table1 t
 join #ids i
    on t.id = i.s

这篇关于从逗号连接的列表创建SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！