Zmienna SQL do przechowywania listy liczb całkowitych


175

Próbuję debugować czyjeś raporty SQL i umieściłem zapytanie bazowe raportów w oknach zapytań SQL 2012.

Jednym z parametrów, o które prosi raport, jest lista liczb całkowitych. Można to osiągnąć w raporcie za pomocą listy rozwijanej wielokrotnego wyboru. Zapytanie bazowe raportu używa tej listy liczb całkowitych w whereklauzuli, np

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

Nie chcę modyfikować zapytania, które debuguję, ale nie mogę dowiedzieć się, jak utworzyć zmienną na serwerze SQL, która może przechowywać tego typu dane, aby ją przetestować.

na przykład

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

Nie ma typu danych, który może przechowywać listę liczb całkowitych, więc jak mogę uruchomić zapytanie raportu na moim serwerze SQL z tymi samymi wartościami co raport?


1
Wiem, że użyłem programu TVP Table Value Parmeter do wstawienia danych, ale teraz jestem pewien, czy można go użyć w miejscu. Dalszy ciąg?
paparazzo

2
dobrze sformułowane pytanie. +1
RayLoveless

Odpowiedzi:


224

Zmienna tabeli

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);    

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

lub

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

2
Dzięki za to, ale znowu wymaga to przepisania sposobu wczytywania zmiennej w zapytaniu. Muszę zachować to samo.
ErickTreetops

3
A co, jeśli nie wiesz, jakie są identyfikatory i które pochodzą z zapytania? Przykład: SET @AddressIDs = (SELECT ID FROM address WHERE Account = 1234)to zapytanie zwróci wiele identyfikatorów i otrzymuję błąd informujący, że podzapytanie zwróciło więcej niż jeden wynik i jest to niedozwolone. Czy istnieje możliwość utworzenia zmiennej, która będzie przechowywać tablicę, jeśli identyfikatory z podzapytania?
Rafael Moreira

1
Wypróbowałem drugą opcję i działa ona dla mniejszej liczby rekordów. Kiedy zwiększam liczbę identyfikatorów, pojawia się błąd TimeOut. Może obsada utrudnia występ.
Użytkownik M

Nie jest to odpowiedź na pierwotne pytanie, ale to odpowiedź na jedno, którego nie zadałem, więc jestem dobry. Przekazuję List <int> jako parametr, ale chcę utworzyć zmienną lokalną do testowania w SSMS. To zabójca.
Wade Hatler

Świetna odpowiedź, zaoszczędziła mi dużo czasu
Alfredo A.

35

Zakładając, że zmienna jest czymś podobnym do:

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

A procedura składowana używa go w tej formie:

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

Możesz utworzyć IntList i wywołać procedurę w następujący sposób:

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

Lub jeśli sam udostępniasz IntList

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

17

Masz rację, w SQL-Server nie ma typu danych, który mógłby przechowywać listę liczb całkowitych. Ale co możesz zrobić, to zapisać listę liczb całkowitych jako ciąg.

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

Następnie możesz podzielić ciąg na oddzielne wartości całkowite i umieścić je w tabeli. Twoja procedura może już to zrobić.

Możesz również użyć dynamicznego zapytania, aby osiągnąć ten sam wynik:

DECLARE @SQL nvarchar(8000);

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

Dzięki, ale znowu musiałbym zmodyfikować zapytanie, na co nie mam pozwolenia.
ErickTreetops

2
Jeśli ktokolwiek z tego korzysta, pamiętaj, że może to być bardzo podatne na wstrzyknięcie SQL, jeśli @listOfIDs jest parametrem ciągu dostarczonym przez użytkownika. W zależności od architektury Twojej aplikacji może to stanowić problem lub nie.
Rogala,

@Rogala Zgoda, użytkownicy będą musieli przeprowadzić własne czynności sanitarne zgodnie z wymaganiami.
Möoz,

1
@ Möoz Zalecam dodanie notatki do odpowiedzi, aby to odzwierciedlić. Nie wszyscy wiedzą i po prostu kopiują i wklejają rozwiązania, nie myśląc o konsekwencjach. Dynamiczny SQL jest BARDZO niebezpieczny i unikam go jak tej plagi.
Rogala

@ Möoz Również nie zagłosowałem na twoją odpowiedź, ale poświęć kilka minut i sprawdź moją odpowiedź? Funkcja STRING_SPLIT jest całkiem fajna i myślę, że będziesz nad nią WSZYSTKO !!
Rogala

6

W przypadku SQL Server 2016+ i Azure SQL Database dodano funkcję STRING_SPLIT, która byłaby idealnym rozwiązaniem tego problemu. Oto dokumentacja: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

Oto przykład:

/*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;

Wynik zapytania to 1,3

~ Pozdrawiam


4

W końcu doszedłem do wniosku, że bez modyfikowania sposobu działania zapytania nie mogę przechowywać wartości w zmiennych. Użyłem profilera SQL, aby złapać wartości, a następnie zakodowałem je na stałe w zapytaniu, aby zobaczyć, jak to działa. Było 18 takich tablic całkowitych, a niektóre miały ponad 30 elementów.

Myślę, że istnieje potrzeba, aby MS / SQL wprowadził do języka kilka dodatkowych typów danych. Tablice są dość powszechne i nie rozumiem, dlaczego nie można ich używać w przechowywanym procencie.


6
SQL Server nie potrzebuje tablic, jeśli ma parametry i zmienne wyceniane w tabeli.
John Saunders

1
Wiemy więc, że zapytanie używa listy liczb całkowitych (przekazanych do niej przez tablicę?). Nie rozumiem, w jaki sposób Twoje zapytanie je wykorzystywało bez użycia jednej z podanych metod w odpowiedziach. Podaj więcej kontekstu, a my pomożemy Ci dalej.
Möoz

2

Nie możesz tego zrobić w ten sposób, ale możesz wykonać całe zapytanie, przechowując je w zmiennej.

Na przykład:

DECLARE @listOfIDs NVARCHAR(MAX) = 
    '1,2,3'

DECLARE @query NVARCHAR(MAX) = 
    'Select *
     From TabA
     Where TabA.ID in (' + @listOfIDs + ')'

Exec (@query)

2
Jak stwierdzono w poprzednim komentarzu, w zależności od sposobu implementacji tego typu rozwiązania należy pamiętać, że może to być podatne na wstrzyknięcie SQL, jeśli @listOfIDs jest parametrem dostarczonym przez użytkownika.
Rogala,

2

Istnieje nowa funkcja w SQL o nazwie, string_splitjeśli używasz listy łańcuchów. Link referencyjny STRING_SPLIT (Transact-SQL)

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

możesz przekazać to zapytanie inw następujący sposób:

SELECT *
  FROM [dbo].[yourTable]
  WHERE (strval IN (SELECT value FROM STRING_SPLIT(@tags, ',') WHERE RTRIM(value) <> ''))

1
Wygląda na prawdopodobne, ale niestety tylko SQL Server 2016.
AnotherFineMess

0

Używam tego :

1-Zadeklaruj zmienną tabeli tymczasowej w skrypcie swojego budynku:

DECLARE @ShiftPeriodList TABLE(id INT NOT NULL);

2-Przydziel do tabeli tymczasowej:

IF (SOME CONDITION) 
BEGIN 
        INSERT INTO @ShiftPeriodList SELECT ShiftId FROM [hr].[tbl_WorkShift]
END
IF (SOME CONDITION2)
BEGIN
    INSERT INTO @ShiftPeriodList
        SELECT  ws.ShiftId
        FROM [hr].[tbl_WorkShift] ws
        WHERE ws.WorkShift = 'Weekend(VSD)' OR ws.WorkShift = 'Weekend(SDL)'

END

3-Odwołaj się do tabeli, gdy jej potrzebujesz, w instrukcji WHERE:

INSERT INTO SomeTable WHERE ShiftPeriod IN (SELECT * FROM @ShiftPeriodList)
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.