Thursday, October 25, 2007

Output Parameter in dynamischem T-SQL

In einer Stored Procedure werden in einem ersten Schritt zwei Variablen gesetzt, die für die weitere Verarbeitung benötigt werden. Dies könnte so aussehen:

SELECT @var1 = Var1, @var2 = Var2
FROM MyTable
WHERE ID = @ID

Nun muss das Statement aber dynamisch zusammengestellt werden und der Select-String danach ausgeführt werden. Dies könnte so aussehen:

SET @query = '
SELECT @var1 = Var1, @var2 = Var2
FROM MyTable
WHERE ID = ' + CAST(@ID as CHAR(15))
EXEC (@query)

In meinem Fall wird das Statement auf einem Hostsystem ausgeführt und der Aufruf muss über Openquery gemacht werden. Dies könnte so aussehen:

SET @query = '
SELECT @var1 = Var1, @var2 = Var2
FROM OPENQUERY(MySystem, ''SELECT Var1, Var2 FROM MyTable WHERE ID = '
+ dbo.MyTypeConverter(@id) + ''')'
EXEC (@query)

Ganz klar, dass die beiden Variablen so nicht mehr abgefüllt werden, sondern zu einem Fehler führen. Wie können diese gesetzt werden?
Gemäss einer ersten Idee wurde eine Variable vom Typ TABLE verwendet und das Openquery-Resultat mit INSERT INTO angefügt. Danach das erste Select-Statement verwendet um die Variablen zu setzten. Funktioniert! Ist aber nicht sehr elegant.
Hübscher ist's so:

SET @params = ' @var1 int OUTPUT, @var2 nvarchar(max) OUTPUT'
SET @query = '
SELECT @var1Out = Var1, @var2Out = Var2
FROM OPENQUERY(MySystem, SELECT Var1, Var2 FROM MyTable '
+ dbo.MyTypeConverter(@id) + ''')'
EXEC sp_executesql @query, @params, @var1Out = @var1 OUTPUT, @var2Out = @var2 OUTPUT

Dieser Ansatz mit der System Procedure sp_executesql bietet zudem noch weitere Vorteile die in der SQL Server Hilfe nachgelesen werden können.

No comments: