sql-server - stored - variables @@ sql server

Variable de SQL para contener la lista de enteros

Question

Estoy tratando de depurar los informes SQL de otra persona y he colocado la consulta de informes subyacente en una ventana de consulta de SQL 2012.

Uno de los parámetros que solicita el informe es una lista de enteros. Esto se logra en el informe a través de un cuadro desplegable de selección múltiple. La consulta subyacente del informe usa esta lista entera en la cláusula where por ejemplo

select *
from TabA
where TabA.ID in (@listOfIDs)

No quiero modificar la consulta que estoy depurando pero no puedo encontrar la manera de crear una variable en SQL Server que pueda contener este tipo de datos para probarla.

p.ej

declare @listOfIDs int
set listOfIDs = 1,2,3,4

No hay ningún tipo de datos que pueda contener una lista de enteros, entonces, ¿cómo puedo ejecutar la consulta del informe en mi SQL Server con los mismos valores que el informe?

Answer 1

Al final llegué a la conclusión de que sin modificar la forma en que funcionaba la consulta, no podía almacenar los valores en las variables. Usé el generador de perfiles de SQL para capturar los valores y luego los codifiqué en la consulta para ver cómo funcionaba. Había 18 de estos conjuntos de enteros y algunos tenían más de 30 elementos en ellos.

Creo que es necesario que MS / SQL introduzca algunos tipos de datos adicionales en el idioma. Las matrices son bastante comunes y no veo por qué no podrías usarlas en un proceso almacenado.

Answer 2

Asumiendo que la variable es algo similar a:

CREATE TYPE [dbo].[IntList] AS TABLE(
[Value] [int] NOT NULL
)

Y el procedimiento almacenado lo está usando de esta forma:

ALTER Procedure [dbo].[GetFooByIds]
@Ids [IntList] ReadOnly
As

Puede crear la IntList y llamar al procedimiento así:

Declare @IDs IntList;
Insert Into @IDs Select Id From dbo.{TableThatHasIds}
Where Id In (111, 222, 333, 444)
Exec [dbo].[GetFooByIds] @IDs

O si está proporcionando la IntList usted mismo

DECLARE @listOfIDs dbo.IntList
INSERT INTO @listofIDs VALUES (1),(35),(118);

Answer 3

No puede hacerlo así, pero puede ejecutar toda la consulta almacenándola en una variable.

Por ejemplo:

DECLARE @listOfIDs NVARCHAR(MAX) =
''1,2,3''
DECLARE @query NVARCHAR(MAX) =
''Select *
From TabA
Where TabA.ID in ('' + @listOfIDs + '')''
Exec (@query)

Answer 4

Para SQL Server 2016+ y Azure SQL Database, se agregó la función STRING_SPLIT que sería la solución perfecta para este problema. Aquí está la documentación: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

Aquí hay un ejemplo:

/*List of ids in a comma delimited string
Note: the '') WAITFOR DELAY ''''00:00:02'''''' is a way to verify that your script
doesn''t allow for SQL injection*/
DECLARE @listOfIds VARCHAR(MAX) = ''1,3,a,10.1,) WAITFOR DELAY ''''00:00:02'''''';
--Make sure the temp table was dropped before trying to create it
IF OBJECT_ID(''tempdb..#MyTable'') IS NOT NULL DROP TABLE #MyTable;
--Create example reference table
CREATE TABLE #MyTable
([Id] INT NOT NULL);
--Populate the reference table
DECLARE @i INT = 1;
WHILE(@i <= 10)
BEGIN
INSERT INTO #MyTable
SELECT @i;
SET @i = @i + 1;
END
/*Find all the values
Note: I silently ignore the values that are not integers*/
SELECT t.[Id]
FROM #MyTable as t
INNER JOIN
(SELECT value as [Id]
FROM STRING_SPLIT(@listOfIds, '','')
WHERE ISNUMERIC(value) = 1 /*Make sure it is numeric*/
AND ROUND(value,0) = value /*Make sure it is an integer*/) as ids
ON t.[Id] = ids.[Id];
--Clean-up
DROP TABLE #MyTable;

El resultado de la consulta es 1,3

~ Saludos

Answer 5

Tiene razón, no hay ningún tipo de datos en SQL-Server que pueda contener una lista de enteros. Pero lo que puedes hacer es almacenar una lista de enteros como una cadena.

DECLARE @listOfIDs varchar(8000);
SET @listOfIDs = ''1,2,3,4'';

Luego puede dividir la cadena en valores enteros separados y ponerlos en una tabla. Tu procedimiento ya puede hacer esto.

También puede usar una consulta dinámica para lograr el mismo resultado:

DECLARE @SQL nvarchar(8000);
SET @SQL = ''SELECT * FROM TabA WHERE TabA.ID IN ('' + @listOfIDs + '')'';
EXECUTE (@SQL);

Answer 6

Variable de tabla

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);
select *
from TabA
where TabA.ID in (select id from @listOfIDs)

o

declare @listOfIDs varchar(1000);
SET @listOfIDs = '',1,2,3,''; --in this solution need put coma on begin and end
select *
from TabA
where charindex('','' + CAST(TabA.ID as nvarchar(20)) + '','', @listOfIDs) > 0