Getting output in a variable from dynamic SQL

Question

I am using a dynamic sql i.e.

DECLARE @searchstring VARCHAR(500)
DECLARE @str VARCHAR(MAX)
SELECT @str = 'SELECT * FROM Table1 WHERE ' + @searchstring
EXECUTE @str

What I need is I want to select one column value from above dynamic sql to pass in a different SP Let's say I need ID column value and pass it to another sp named GetAnotherData @Ids. How can I do that?

Chekc this post : http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable — Lokesh, Sep 22 '13 at 06:31
Do you want to select a single row or all rows in the column. Could you clarify that, please? — Aleksandr Fedorenko, Sep 22 '13 at 06:39

score 5 · Accepted Answer · answered Sep 22 '13 at 07:44

well you can go with Alexander Fedorenko example, but if you don't want to create any temp tables, you can use output xml parameter to pass your ids:

declare @stmt nvarchar(max), @Data xml, @searchstring nvarchar(max)

select @stmt = '
    select @Data = (
        select id
        from Table1
        where ' + @searchstring + '
        for xml raw(''Data'')
    )
'

exec sp_executesql
    @stmt = @stmt,
    @params = N'@Data xml output',
    @Data = @Data output

select
    T.C.value('@id', 'int') as id
from @Data.nodes('Data') as T(C)

sql fiddle demo

Aleksandr Fedorenko · Answer 2 · 2013-09-22T07:35:34.170

0

The following example creates a user-defined table type that has one Id column. Further the INSERT @RetIds EXEC(@STR) statement fills the parameter list, and then passes the values to a stored procedure

CREATE TYPE RetIds AS TABLE
 (
  Id int
  )

DECLARE @searchstring varchar(500) = 'AND SearchCol = 1'
DECLARE @str varchar(max)
SELECT @str ='SELECT Id FROM dbo.test6 WHERE 1 = 1 ' + @searchstring

DECLARE @RetIds AS RetIds
INSERT @RetIds
EXEC(@str)
EXEC dbo.ExecIds @RetIds

See demo on SQLFiddle

edited Sep 22 '13 at 07:35

answered Sep 22 '13 at 06:57

Aleksandr Fedorenko

16,594
6
37
44

As far as I know there is no way to pass the data set to a stored procedure without creating a temp table. No magic bullet here;)... – Aleksandr Fedorenko Sep 22 '13 at 07:37
@AlexanderFedorenko there's a way with xml parameter :) don't know if OP'll like it – Roman Pekar Sep 22 '13 at 07:50
Ok. This means we can pass xml type to a stored procedure. It's fun, a little bit awkward, but interesting;)catch +1 Roman – Aleksandr Fedorenko Sep 22 '13 at 08:07
@AlexanderFedorenko thanks, yes, it is a bit awkward that's why not all my collegaues use it :) but I personally like xml support in SQL Server, it often helps me – Roman Pekar Sep 22 '13 at 08:15

Getting output in a variable from dynamic SQL

2 Answers2