SQL Server：因为动态内容 [英] SQL Server: In cause with dynamic content

问题描述

我想使用In cause with dynamic variable，



例如：



声明@MultipleCategory nvarchar（20）

set @MultipleCategory ='2,3'



从视频加入类别中选择video.videoid对videoid = category.videoid

WHERE category.CategoryID IN（@MultipleCategory）



但它不起作用。



我知道我可以使用动态SQL但是如果我们有大量查询它会很有竞争力。

Hi, I want to use In cause with dynamic variable,

Example :

declare @MultipleCategory nvarchar(20)
set @MultipleCategory ='2,3'

select video.videoid from video join category on videoid=category.videoid
WHERE category.CategoryID IN (@MultipleCategory)

but it is not working.

I know i can use dynamic SQL but it will competitive, if we have large query.

推荐答案

简短的回答是：你可以't。



但是有很多变通方法。 Erland Sommarskog [ ^ ]救援。

The short answer is: You can't.

But there are plenty of workarounds. Erland Sommarskog[^] to the rescue.

首先，它是一个条款，不是因为^ _ ^



其次，in子句使用一组输入，即具有一列的表结构。您在in子句中的内容是一个项目。一个varchar。



如果是这样写的：

First, it is a clause, not cause ^_^

Second, the "in" clause works off of a set of inputs, that is a table like structure with one column. What you have in your "in" clause is one item. A varchar.

If it was written like this:

WHERE category.CategoryID IN (2,3)

这样可行。你有：

That would work. You have:

WHERE category.CategoryID IN ('2,3') which won't even match by type.

如果必须使用csv传入列表，然后您需要将其转换为表列。您可以使用csv解析器表值函数。存在许多示例，我将在底部链接一个。



这可以使用如下：

If you must use a csv to pass in your list, then you will need to convert it into a table column. You can use a csv parser Table Valued Function for this. Many examples exist and I will link one at the bottom.

This can be used as follows:

WHERE category.CategoryID IN ([dbo].[GetMyTable](@MultipleCategory))

这里是链接：

将CSV作为表格返回的SQL函数 [ ^ ]



该函数一次遍历字符串一个字符串，因此它非常可扩展

here is the link:
SQL function to return CSV as a table[^]

The function steps through the string one char at a time so it quite scaleable

这篇关于SQL Server：因为动态内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！