Liste mit Werten an SP übergeben

[deitysou]

Hi,

gibt es eine Möglichkeit, einer Stored Procedure eine Liste von Werten zu übergeben?
In meinem Fall brauche ich diese Möglichkeit dafür, um eine Liste von IDs an die Datenbank zu übergeben.

MfG
deitysou

[Andreas Kosch]

Hallo,
bei dieser Aufgabe wird üblicherweise eine Zeichenkette an die Stored Procedure übergeben, in der alle ID-Werte durch einen Separator (wie zum Beispiel ein Komma) unterteilt sind. Die Stored Procedure verwendet eine Funktion, um die ID-Liste in eine TABLE mit ID-Datensätzen umzuwandeln. Das folgende Beispiel demonstriert das Prinzip:

<div style="font-family: Courier New; font-size: 10pt; color: black; background: white; border-top: windowtext 1pt solid; padding-top: 0pt; border-left: windowtext 1pt solid; padding-left: 0pt; border-right: windowtext 1pt solid; padding-right: 0pt; border-bottom: windowtext 1pt solid; padding-bottom: 0pt;"><p style="margin: 0px;"><span style="color: blue;">USE </span>tempdb</p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Funktion splittet die Zeichenkette in eine TABLE auf</span></p><p style="margin: 0px;"><span style="color: blue;">CREATE </span>FUNCTION fnSplitCommaListToID</p><p style="margin: 0px;">( </p><p style="margin: 0px;">&nbsp; @CommaList&nbsp; <span style="color: blue;">VARCHAR</span>(1000)</p><p style="margin: 0px;">) RETURNS @IDTable <span style="color: blue;">TABLE</span>(id <span style="color: blue;">INT</span>)</p><p style="margin: 0px;"><span style="color: blue;">AS</span></p><p style="margin: 0px;"><span style="color: blue;">BEGIN</span></p><p style="margin: 0px;">&nbsp; <span style="color: blue;">DECLARE </span>@FC <span style="color: blue;">int</span></p><p style="margin: 0px;">&nbsp; <span style="color: blue;">SET </span>@FC = -1</p><p style="margin: 0px;">&nbsp; <span style="color: blue;">WHILE </span>(Len(@CommaList) &gt; 0)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">BEGIN</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp; <span style="color: blue;">SET </span>@FC = <span style="color: blue;">CHARINDEX</span>(<span style="color: maroon;">',' </span>, @CommaList)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp; <span style="color: blue;">IF </span>(@FC = 0) <span style="color: blue;">AND </span>(LEN(@CommaList) &gt; 0)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp; <span style="color: blue;">BEGIN</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">INSERT INTO </span>@IDTable <span style="color: blue;">VALUES </span>(<span style="color: blue;">CAST</span>(@CommaList <span style="color: blue;">AS INT</span>))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp; <span style="color: blue;">BREAK</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">END</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">IF </span>(@FC &gt; 1)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp; <span style="color: blue;">BEGIN</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">INSERT INTO </span>@IDTable <span style="color: blue;">VALUES </span>(<span style="color: blue;">CAST</span>(<span style="color: blue;">LEFT</span>(@CommaList, @FC - 1) <span style="color: blue;">AS INT</span>))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">SET </span>@CommaList = <span style="color: blue;">RIGHT</span>(@CommaList, (LEN(@CommaList) - @FC))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp; <span style="color: blue;">END</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">ELSE </span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp; <span style="color: blue;">SET </span>@CommaList = <span style="color: blue;">RIGHT</span>(@CommaList, (LEN(@CommaList) - @FC))</p><p style="margin: 0px;">&nbsp; <span style="color: blue;">END</span></p><p style="margin: 0px;">&nbsp; <span style="color: blue;">RETURN</span></p><p style="margin: 0px;"><span style="color: blue;">END</span></p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Testdaten</span></p><p style="margin: 0px;"><span style="color: blue;">CREATE TABLE </span>TestTbl</p><p style="margin: 0px;">(</p><p style="margin: 0px;">&nbsp; testtbl_id <span style="color: blue;">INT </span>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; <span style="color: blue;">NOT NULL IDENTITY PRIMARY KEY</span>,</p><p style="margin: 0px;">&nbsp; wert&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; <span style="color: blue;">VARCHAR</span>(9) <span style="color: blue;">NOT NULL</span></p><p style="margin: 0px;">)</p><p style="margin: 0px;">GO</p><p style="margin: 0px;"><span style="color: blue;">INSERT INTO </span>TestTbl (wert) <span style="color: blue;">VALUES</span>(<span style="color: maroon;">'Test 1'</span>);</p><p style="margin: 0px;"><span style="color: blue;">INSERT INTO </span>TestTbl (wert) <span style="color: blue;">VALUES</span>(<span style="color: maroon;">'Test 2'</span>);</p><p style="margin: 0px;"><span style="color: blue;">INSERT INTO </span>TestTbl (wert) <span style="color: blue;">VALUES</span>(<span style="color: maroon;">'Test 3'</span>);</p><p style="margin: 0px;"><span style="color: blue;">INSERT INTO </span>TestTbl (wert) <span style="color: blue;">VALUES</span>(<span style="color: maroon;">'Test 4'</span>);</p><p style="margin: 0px;"><span style="color: blue;">INSERT INTO </span>TestTbl (wert) <span style="color: blue;">VALUES</span>(<span style="color: maroon;">'Test 5'</span>);</p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Stored Procedure verwendet die Funktion, um die ID-Liste </span></p><p style="margin: 0px;"><span style="color: green;">-- in eine Ergebnismenge aus INT-Werten umzuwandeln</span></p><p style="margin: 0px;"><span style="color: blue;">CREATE PROCEDURE </span>spSplitCommaListToIDDemo</p><p style="margin: 0px;">(</p><p style="margin: 0px;">&nbsp; @CommaList&nbsp; <span style="color: blue;">VARCHAR</span>(1000)</p><p style="margin: 0px;">)</p><p style="margin: 0px;"><span style="color: blue;">AS</span></p><p style="margin: 0px;">&nbsp; <span style="color: blue;">SET NOCOUNT ON</span>;</p><p style="margin: 0px;">&nbsp; <span style="color: blue;">SELECT </span>* <span style="color: blue;">FROM </span>dbo.TestTbl </p><p style="margin: 0px;">&nbsp; <span style="color: blue;">WHERE </span>testtbl_id <span style="color: blue;">IN </span>(<span style="color: blue;">SELECT </span>id <span style="color: blue;">FROM </span>fnSplitCommaListToID(@CommaList));</p><p style="margin: 0px;"><span style="color: blue;">RETURN </span>@@ROWCOUNT;</p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Stored Procedure testen</span></p><p style="margin: 0px;"><span style="color: blue;">EXEC </span>spSplitCommaListToIDDemo <span style="color: maroon;">'2,4'</span>;</p></div&gt

[deitysou]

Danke :