ejemplos - SQL Server-cláusula In con una variable declarada

like en sql ejemplos

Question

Esta pregunta ya tiene una respuesta aquí:

• Parametrizar una cláusula 38 de SQL IN respuestas

Digamos que obtuve lo siguiente:

DECLARE @ExcludedList VARCHAR(MAX)

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

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

Error: la conversión falló al convertir el valor varchar '','' al tipo de datos int.

Entiendo por qué el error está ahí, pero no sé cómo resolverlo ...

Answer 1

Este es un ejemplo en el que utilizo la variable de tabla para listar múltiples valores en una cláusula IN. La razón obvia es poder cambiar la lista de valores solo en un lugar en un procedimiento largo.

Para hacerlo aún más dinámico y permitir la entrada del usuario, sugiero declarar una variable varchar para la entrada, y luego usar un WHILE para recorrer los datos en la variable e insertarlos en la variable de la tabla.

Reemplaza @your_list, Your_table y los valores con cosas reales.

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 )

El extracto de declaración seleccionado hará lo mismo que:

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

Answer 2

Creo que el problema está en

3 + '', '' + 4

cambiarlo a

''3'' + '', '' + ''4''

DECLARE @ExcludedList VARCHAR(MAX)

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

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

SET @ExcludedListe para que su consulta se convierta

ya sea

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

o

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

Answer 3

Necesitas ejecutar esto como un sp dinámico como

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

Answer 4

No puede usar una variable en una cláusula IN : necesita usar SQL dinámico o usar una función (TSQL o CLR) para convertir la lista de valores en una tabla .

Ejemplo de SQL dinámico:

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

Answer 5

Primero, crea una función rápida que dividirá una lista delimitada de valores en una tabla, como esta:

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 

Luego llama a la función de esta manera ...

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

Esto ha funcionado muy bien en nuestro proyecto ...

Por supuesto, también se podría hacer lo contrario, si ese fuera el caso (aunque no es su pregunta).

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

Y esto realmente es útil cuando se trata de informes que tienen una lista de parámetros de selección múltiple opcional. Si el parámetro es NULL, quiere que se seleccionen todos los valores, pero si tiene uno o más valores, quiere que los datos del informe se filtren en esos valores. Luego usa SQL así:

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

De esta forma, si @ExcludeList es un valor NULL, la cláusula OR de la unión se convierte en un interruptor que desactiva el filtrado en este valor. Muy útil...

Answer 6

Prueba esto:

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

Y luego llámalo así:

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

Answer 7

Tengo otra solución para hacerlo sin consulta dinámica. Podemos hacerlo con la ayuda de xquery también.

    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)

Aquí está la solución completa: http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/

Answer 8

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)

Sé que estoy respondiendo a una publicación anterior, pero quería compartir un ejemplo de cómo usar tablas de variables cuando uno quiere evitar el uso de SQL dinámico. No estoy seguro de si es la forma más eficiente, sin embargo, esto ha funcionado en el pasado para mí cuando el SQL dinámico no era una opción.