需要在单个实例中从sql查询中赋值

Question

我正在sql-server-2008-R2中执行下面的查询，它为我提供了我想要的输出(即行数)，但是，我希望将结果以多列行的形式存储在一个变量中。

declare @rCount int
declare @kuri nvarchar(max)
declare @IDs nvarchar(max)

select @rCount=10

set @kuri='select top '+cast(@rCount as varchar)+' FLD295,FLD9 from tableName (nolock) ORDER BY NEWID()'

execute(@kuri)

我之前已经这样做了，但这是不同的查询，我需要将这两个查询的结果连接到一个列中(赋值和显示)。

select @IDs=CAST(isnull(@IDs ,'')as varchar)+CAST(COALESCE(fld9,',')as varchar) from table1307 (nolock) ORDER BY NEWID()

Answer 1

DECLARE @T1 TABLE (
    FLD9 bigint
, FLD295 nVARCHAR(max)
)

insert into @T1(,FLD295,FLD9) 
select top(@rCount) FLD295,FLD9 from tableName (nolock)
 ORDER BY NEWID()

select * from @T1
select @IDs=CAST(isnull(@IDs ,'')as varchar)+CAST(COALESCE(fld9,',')as varchar) from @T1 

Answer 2

我觉得你错过了你能做的那张脸

declare @i int=3

select top(@i) .... –

2008确实支持动态顶级

我希望你知道结果应该是单身。，否则，这是不可能的(除非您正在执行insert into...

如果您询问如何连接( cte是jsut表，请忽略它)

​

​

Answer 3

您可以将游标用于访问结果集：

declare @rCount int
declare @kuri nvarchar(max)
declare @IDs nvarchar(max)

declare @FLD295 int
declare @FLD9 int

select @rCount=10

set @kuri='declare cursor curs for select top '+cast(@rCount as varchar)+' FLD295,FLD9 from tableName (nolock) ORDER BY NEWID()'

exec sp_executesql @kuri

open curs
fetch from curs
into @FLD295, @FLD9

while @@fetch_status = 0
BEGIn


fetch from curs
into @FLD295, @FLD9
END

close curs
deallocate curs