从动态SQL中获取变量的输出。

I am using a dynamic sql i.e.

我使用的是动态sql，即。

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?

我需要从上面的动态sql中选择一个列值来传递一个不同的SP，比方说我需要ID列值，然后将它传递给另一个名为GetAnotherData @ ID的SP。我怎么做呢?

2 个解决方案

#1

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:

您可以使用Alexander Fedorenko示例，但是如果您不想创建任何临时表，您可以使用output xml参数来传递您的id:

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

sql小提琴演示

#2

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

下面的示例创建具有一个Id列的用户定义表类型。此外，INSERT @RetIds EXEC(@STR)语句填充了参数列表，然后将值传递给一个存储过程

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

看到演示SQLFiddle

#1