wake-up-neo.com

SQL Server-Prozedur deklarieren eine Liste

Mein SQL-Code ist ziemlich einfach. Ich versuche, einige Daten aus einer Datenbank wie folgt auszuwählen:

SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)

Ich möchte wissen, wie man die Liste vor dem select deklariert (in einer Variablen, Liste, einem Array oder etwas anderem) und innerhalb des select nur den Variablennamen verwendet. 

VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList
sqlsql-serversql-server-2008-r2
28
Alex Doro

Sie können eine Variable wie folgt als temporäre Tabelle deklarieren:

declare @myList table (Id int)

Was bedeutet, dass Sie die Anweisung insert verwenden können, um sie mit Werten zu füllen:

insert into @myList values (1), (2), (5), (7), (10)

Dann kann Ihre select-Anweisung entweder die in-Anweisung verwenden:

select * from DBTable
where id in (select Id from @myList)

Oder Sie könnten wie folgt an der temporären Tabelle teilnehmen:

select *
from DBTable d
join @myList t on t.Id = d.Id

Wenn Sie so etwas häufig tun, können Sie einen benutzerdefinierten Tabellentyp definieren , um Ihre Variable dann wie folgt zu deklarieren:

declare @myList dbo.MyTableType
49
Peter Monks

Dies ist bei einer normalen Abfrage nicht möglich, da die in-Klausel separate Werte und keinen einzelnen Wert benötigt, der eine durch Kommas getrennte Liste enthält. Eine Lösung wäre eine dynamische Abfrage

declare @myList varchar(100)
set @myList = '(1,2,5,7,10)'
exec('select * from DBTable where id IN ' + @myList)
8
juergen d

Wenn Sie eine durch Kommas getrennte Zeichenfolge als Eingabe verwenden und diese in Abfrage anwenden möchten, können Sie die Funktion wie folgt definieren: 

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
    returns @temptable TABLE (items varchar(MAX))       
    as       
    begin      
        declare @idx int       
        declare @slice varchar(8000)       

        select @idx = 1       
            if len(@String)<1 or @String is null  return       

        while @idx!= 0       
        begin       
            set @idx = charindex(@Delimiter,@String)       
            if @idx!=0       
                set @slice = left(@String,@idx - 1)       
            else       
                set @slice = @String       

            if(len(@slice)>0)  
                insert into @temptable(Items) values(@slice)       

            set @String = right(@String,len(@String) - @idx)       
            if len(@String) = 0 break       
        end   
    return 
    end;

Sie können es wie folgt verwenden:

Declare @Values VARCHAR(MAX);

set @Values ='1,2,5,7,10';
Select * from DBTable
    Where id  in (select items from [dbo].[Split] (@Values, ',') )

Alternativ, wenn Sie keine durch Kommas getrennte Zeichenfolge als Eingabe verwenden, können Sie Table variable OR TableType versuchen oder Temp table wie folgt: INSERT mit LIST in Stored Procedure

1
Pranav Singh

In solchen Situationen habe ich es immer leichter gefunden, den Test gegen die Liste zu invertieren. Zum Beispiel...

SELECT 
    field0, field1, field2 
FROM 
    my_table 
WHERE 
    ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'

Dies bedeutet, dass für die Werte, nach denen Sie suchen, kein komplizierter Mischmash erforderlich ist.

Wenn unsere Liste beispielsweise ('1,2,3') war, fügen wir am Anfang und Ende unserer Liste ein Komma hinzu: ',' + @mysearchlist + ','.

Wir machen dasselbe für den Feldwert, nach dem wir suchen, und fügen Platzhalter hinzu: '%,' + CAST(field3 AS VARCHAR) + ',%' (beachten Sie die Zeichen % und ,).

Abschließend testen wir die beiden mit dem Operator LIKE: ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'.

1
Paul

Alternative zu @Peter Mönchen.

Wenn die Zahl in der Anweisung 'in' klein und fest ist.

DECLARE @var1 varchar(30), @var2 varchar(30), @var3  varchar(30);

SET @var1 = 'james';
SET @var2 = 'same';
SET @var3 = 'dogcat';

Select * FROM Database Where x in (@var1,@var2,@var3);
1
Aaron C

Sie können die Liste der übergebenen Werte in einen Tabellenwertparameter konvertieren und dann diese Liste auswählen 

DECLARE @list NVARCHAR(MAX)
SET @list = '1,2,5,7,10';

DECLARE @pos INT
DECLARE @nextpos INT
DECLARE @valuelen INT
DECLARE @tbl TABLE (number int NOT NULL)

SELECT @pos = 0, @nextpos = 1;

WHILE @nextpos > 0
BEGIN
    SELECT @nextpos = charindex(',', @list, @pos + 1)
    SELECT @valuelen = CASE WHEN @nextpos > 0
                            THEN @nextpos
                            ELSE len(@list) + 1
                        END - @pos - 1
    INSERT @tbl (number)
        VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
    SELECT @pos = @nextpos;
END

SELECT * FROM DBTable WHERE id IN (SELECT number FROM @tbl);

In diesem Beispiel wird der in '1,2,5,7,10' übergebene String von den Kommas getrennt und jeder Wert wird als neue Zeile in die Tabellenvariable @tbl eingefügt. Dies kann dann anhand von Standard-SQL ausgewählt werden.

Wenn Sie diese Funktionalität wiederverwenden möchten, können Sie weitergehen und diese Funktion in eine Funktion umwandeln.

0
Jonathan