SQL Server - В разделе с объявленной переменной

Ответ 1

Вам нужно выполнить это как динамическое sp как

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3,4,22,6014'
declare @sql nvarchar(Max)

Set @sql='SELECT * FROM [A] WHERE Id NOT IN ('[email protected]+')'

exec sp_executesql @sql

Ответ 2

Это пример, когда я использую переменную таблицы для перечисления нескольких значения в предложении IN. Очевидная причина состоит в том, чтобы иметь возможность изменять список значений только в одном месте в длинной процедуре.

Чтобы сделать его еще более динамичным и с учетом ввода пользователем, я предлагаю объявляя переменную varchar для ввода, а затем используя WHILE для перебирать данные в переменной и вставлять их в таблицу переменная.

Замените @your_list, Your_table и значения на реальные вещи.

DECLARE @your_list TABLE (list varchar(25)) 
INSERT into @your_list
VALUES ('value1'),('value2376')

SELECT *  
FROM your_table 
WHERE your_column in ( select list from @your_list )

Оператор select select будет делать то же самое, что:

SELECT *  
FROM your_table 
WHERE your_column in ('value','value2376' )

Ответ 3

DECLARE @IDQuery VARCHAR(MAX)
SET @IDQuery = 'SELECT ID FROM SomeTable WHERE Condition=Something'
DECLARE @ExcludedList TABLE(ID VARCHAR(MAX))
INSERT INTO @ExcludedList EXEC(@IDQuery)    
SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

Я знаю, что я отвечаю на старый пост, но я хотел бы поделиться примером использования таблиц переменных, когда нужно избегать использования динамического SQL. Я не уверен, что это самый эффективный способ, однако это работало в прошлом для меня, когда динамический SQL не был вариантом.

Ответ 4

Сначала создайте быструю функцию, которая разделит список таблиц с разделителями на таблицу, например:

CREATE FUNCTION dbo.udf_SplitVariable
(
    @List varchar(8000),
    @SplitOn varchar(5) = ','
)

RETURNS @RtnValue TABLE
(
    Id INT IDENTITY(1,1),
    Value VARCHAR(8000)
)

AS
BEGIN

--Account for ticks
SET @List = (REPLACE(@List, '''', ''))

--Account for 'emptynull'
IF LTRIM(RTRIM(@List)) = 'emptynull'
BEGIN
    SET @List = ''
END

--Loop through all of the items in the string and add records for each item
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN

    INSERT INTO @RtnValue (value)
    SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@SplitOn, @List)-1)))  

    SET @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List) + LEN(@SplitOn), LEN(@List))

END

INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))

RETURN

END 

Затем вызовите функцию, подобную этой...

SELECT * 
FROM A
LEFT OUTER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
WHERE f.Id IS NULL

Это хорошо отразилось на нашем проекте...

Конечно, можно было бы сделать обратное, если бы это было так (хотя и не ваш вопрос).

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value

И это действительно полезно при работе с отчетами, которые имеют необязательный список параметров с несколькими выборами. Если параметр NULL, вы хотите, чтобы все значения были выбраны, но если у него есть одно или несколько значений, вы хотите, чтобы данные отчета были отфильтрованы по этим значениям. Затем используйте SQL следующим образом:

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value OR @ExcludeList IS NULL

Таким образом, если @ExcludeList является значением NULL, предложение OR в соединении становится переключателем, который отключает фильтрацию этого значения. Очень удобно...

Ответ 5

Вы не можете использовать переменную в предложении IN - вам нужно использовать динамический SQL или использовать функция (TSQL или CLR) для преобразования списка значений в таблицу.

Пример динамического SQL:

DECLARE @ExcludedList VARCHAR(MAX)
    SET @ExcludedList = 3 + ',' + 4 + ',' + '22'

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT * FROM A WHERE Id NOT IN (@ExcludedList) '

 BEGIN

   EXEC sp_executesql @SQL '@ExcludedList VARCHAR(MAX)' @ExcludedList

 END

Ответ 6

Я думаю, проблема в

3 + ', ' + 4

измените его на

'3' + ', ' + '4'

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3' + ', ' + '4' + ' ,' + '22'

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

SET @ExcludedListe, чтобы ваш запрос стал

либо

SELECT * FROM A WHERE Id NOT IN ('3', '4', '22')

или

SELECT * FROM A WHERE Id NOT IN (3, 4, 22)

Ответ 7

Попробуйте следующее:

CREATE PROCEDURE MyProc @excludedlist integer_list_tbltype READONLY AS
  SELECT * FROM A WHERE ID NOT IN (@excludedlist)

И затем назовите его следующим образом:

DECLARE @ExcludedList integer_list_tbltype
INSERT @ExcludedList(n) VALUES(3, 4, 22)
exec MyProc @ExcludedList

Ответ 8

У меня есть другое решение, чтобы сделать это без динамического запроса. Мы также можем сделать это с помощью xquery.

    SET @Xml = cast(('<A>'+replace('3,4,22,6014',',' ,'</A><A>')+'</A>') AS XML)
    Select @Xml

    SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)

Вот полное решение: http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/