Does Altace Have Sugar In It

output parameters in a dynamic T-SQL

In a stored procedure in a first step, two variables are set that are required for further processing. This could look like this:

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


Now the statement but must be compiled on the fly and the Select-String to run it. This could look like this:

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

In my case the statement is executed on a host system and the call must be made through Open Query. This could look like this:

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

very clear that the two variables thus no longer be bottled, but give an error. How can they be enforced?
According to a first idea was using a variable of type TABLE and added to the open query results with INSERT INTO. Then use the first select statement in order to set the variables. Works! Is not very elegant.
's prettier like this: SET @ params =

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

This approach to the Procedure sp_executesql system offers additional advantages in the SQL Server Help can be found.