动态选择查询中的问题 [英] Problem in dynamic select query

问题描述

我在创建动态查询时遇到问题，



首先，我运行了以下查询我得到三行，

I have problem with creating dynamic query,

First, I run the following query I get three rows,

select * from HumanResources.Employee where BusinessEntityID  in (1,2,3)

但是，当我创建这样的动态查询时，

but, when I create dynamic query like this,

declare @BussinessEntityID varchar(50)
set @BussinessEntityID = '1,2,3'
select * from HumanResources.Employee where CONVERT(varchar(50), BusinessEntityID ) in (@BussinessEntityID)

然后它返回0行，



谁能告诉我哪里错了？



谢谢。！

then it returns 0 rows,

can anyone tell me where I am wrong?

Thank You.!

推荐答案

是。

你可以不提供变量字符串作为IN子句的源 - 字符串必须是常量。

添加此函数：

Yes.
You can''t supply a variable string as the source for an IN clause - the string has to be constant.
Add this function:

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
	SET @InStr = REPLACE(@InStr + ',', ',,', ',')
	DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
	RETURN
END

然后

Then

DECLARE @BusinessEntityID VARCHAR(50)
SET @BusinessEntityID = '1,2,3'
SELECT * FROM myTable where id IN (SELECT * FROM dbo.CSVToTable(@BusinessEntityID))

Griff'的答案绝对是co直到，但解释并不完整。如果仔细查看自己的工作陈述，可以看到，你在（1,2,3）中使用，而在''1中使用 ，2,3''，1,2,3 中既不是。你在括号中给出的不是字符串常量，它是一个常量，或者如果你愿意的话，表值是常数。



好​​吧，如果你对待它这样，它会起作用。有几种方法（除了格里夫给出的一种方法），但想法是一样的：使它成为一个可用于子查询的集合。



Applorach 1：声明一个表值变量，并填写：



Griff''s answer is absolutely correct, the explanation is not complete though. If you look carefully to your own working statement, you can see, that you used in (1,2,3), not in ''1,2,3'', neither in 1,2,3. What you have given in the parenthesis is not a string constant, it is a set constant, or a table valued constant if you like.

Well, if you treat it like that, it will work. There are several approaches (besides the one Griff has given), but the idea is the same: make it a set to be usable in a subquery.

Applorach 1: declare a table valued variable, and fill:

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

替代以上代码的插入：

Alternative to inserts for the above code:

INSERT INTO @MyList(Value)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4

方法2：使用动态查询：

Approach 2: Use dynamic query:

DECLARE @sql nvarchar(max)
declare @list varchar(256)
set @list = '1,2,3'
SELECT @sql = 'SELECT * FROM MyTable WHERE MyColumn IN (' + @list + ')'

exec sp_executeSQL @sql

在这种方法中，你可以像你一样构造查询，但是作为字符串，而不是执行它。它有一些限制，但它也有效。

In this approach you construct the query as you did but as string, and than execute it. It has some limitations, but it works also.

这篇关于动态选择查询中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！