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?
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)
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
