SQL Server - в предложении с объявленной переменной

Допустим, я получил следующее:

DECLARE @ExcludedList VARCHAR(MAX)

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

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

Ошибка: преобразование не удалось при преобразовании значения varchar ', ' в тип данных int.

Я понимаю, почему ошибка, но я не знаю, как ее решить...

Источник

8 ответов

Решение

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

DECLARE @ExcludedList VARCHAR(MAX)

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

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

exec sp_executesql @sql
Источник

Это пример, где я использую переменную таблицы, чтобы перечислить несколько значений в предложении 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 abowe сделает то же самое, что и:

SELECT *  
FROM your_table 
WHERE your_column in ('value','value2376' )
Источник 
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 не был вариантом.

Источник

Вы не можете использовать переменную в 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
Источник

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

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 в соединении становится переключателем, который отключает фильтрацию для этого значения. Очень кстати...

Источник

Попробуй это:

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
Источник

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

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)
Источник

У меня есть другое решение, чтобы сделать это без динамического запроса. Мы можем сделать это и с помощью 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/

Источник
Другие вопросы по тегам