========== 使用表变量无法应用 Insert into exec ** Select table into ** 不适合批量处理而用临时表却:Create table #tmp([name] nvarchar(100)) Declare @name nvarchar(100)insert into #tmp exec ph_getname N'1008',@name outputselect [name] from #tmp --print @name --Drop table #tmp GO同样要用DROP语句,而Select后结果为空(不是NULL,而是没有结果),过程返回值正常!
=======回: zjcxc(邹建)在问题里面:set @s=N'select top 20 .....' exec sp_executesql @s....output...执行后结果是不是应该有20条记录! 但是照该代码里,我一次只能取出第一条记录!所以问题就在这里!
... FETCH NEXT FROM c_a INTO @cTableName,@cGameName--定义一个保存数据的表变量 declare @t table( uid varchar(100), gradename varchar(100), grade bigint, score bigint --还要保存什么自己添加 )WHILE @@FETCH_STATUS = 0 BEGIN set @s=N'select top 20 @uid=uid, @gradename=gradename, @grade=grade, @score=score from '+@cTableName if @cTableName=N't_1' or @cTableName=N't_2' set @s=@s+N' order by money desc' else set @s=@s+N' order by grade desc,score desc' exec sp_executesql @s,N'@uid varchar(100) output, @gradename varchar(100) output, @grade bigint output, @score bigint output', @uid output,@gradename output,@grade output,@score output exec ph_getname @uid,@name output--自定义过程 exec ph_getschool @uid,@cSchoolName output--自定义过程 ----怎么样才能获得这20条数据--- --先把数据存到表变量(对应要保存的变量) insert @t select @uid,@gradename,@grade,@scoreFETCH NEXT FROM c_a INTO @cTableName,@cGameName END --完成后显示结果 select * from @t ...
非常感谢zjcxc(邹建)为我分忧! 但是,这样,得到的还是每个表里的第一行记录,而没有得到top 20的结果!现在我把问题简化了:set @s="select top 20 @uid=uid from table" exec sp_executesql @s,N'@uid varchar(100) output',@uid output真抱歉,我的表达能力有限,呵呵.我是想得到前20个用户的UID,该怎么办?
再就是,我这样用时:set @s="select top 20 @uid=uid from table" insert #t exec sp_executesql @s,N'@uid varchar(100) output',@uid output select * from #t为什么都是空的!?
----------------proc1 create table #t(uid varchar(100))insert #t exec(N'select top 10 uid from table') select * from #t drop table #t----这样可以得到结果集 -------------------proc2 create table #t(uid varchar(100)) Declare @s nvarchar(200),@uid varchar(20)set @s=N'select top 10 @uid=uid from table' insert #t exec sp_executesql @s,N'@uid varchar(20) output',@uid output select * from #t drop table #t --------为什么这样不可以呢?
select uid into #t from table where 1<>1 declare @Sql varchar(800) declare @str varchar(8000) set @str='insert into #t(uid) ' set @Sql = ' SELECT top 20 uid FROM table set @str=@str+@Sql exec(@str)
--一个变量只能保存一个uid --用游标declare tb cursor local for select top 20 uid from table open tb fetch tb into @uid while @@fetch_stauts=0 begin --- 处理 fetch tb into @uid end close tb deallocate tb
2.调用
insert into #temptable exec @uid,@name output
-----你這樣在存儲過程裡建立臨時表保持結果的話,隻要存儲過程一結束,它會給系統自動刪除
但是代码里是在一个WHILE里,只定义一个表变量行吗?
如果只用一个变量,结果集会成倍增长,处理又变得麻烦!如果要定义多个变量那又如何做?====我想这不太合适吧!
使用表变量无法应用
Insert into exec **
Select table into **
不适合批量处理而用临时表却:Create table #tmp([name] nvarchar(100))
Declare @name nvarchar(100)insert into #tmp exec ph_getname N'1008',@name outputselect [name] from #tmp
--print @name
--Drop table #tmp
GO同样要用DROP语句,而Select后结果为空(不是NULL,而是没有结果),过程返回值正常!
exec sp_executesql @s....output...执行后结果是不是应该有20条记录!
但是照该代码里,我一次只能取出第一条记录!所以问题就在这里!
FETCH NEXT FROM c_a INTO @cTableName,@cGameName--定义一个保存数据的表变量
declare @t table(
uid varchar(100),
gradename varchar(100),
grade bigint,
score bigint
--还要保存什么自己添加
)WHILE @@FETCH_STATUS = 0
BEGIN
set @s=N'select top 20 @uid=uid,
@gradename=gradename,
@grade=grade,
@score=score from '+@cTableName
if @cTableName=N't_1' or @cTableName=N't_2'
set @s=@s+N' order by money desc'
else
set @s=@s+N' order by grade desc,score desc' exec sp_executesql @s,N'@uid varchar(100) output,
@gradename varchar(100) output,
@grade bigint output,
@score bigint output',
@uid output,@gradename output,@grade output,@score output exec ph_getname @uid,@name output--自定义过程
exec ph_getschool @uid,@cSchoolName output--自定义过程
----怎么样才能获得这20条数据--- --先把数据存到表变量(对应要保存的变量)
insert @t select @uid,@gradename,@grade,@scoreFETCH NEXT FROM c_a INTO @cTableName,@cGameName
END
--完成后显示结果
select * from @t
...
但是,这样,得到的还是每个表里的第一行记录,而没有得到top 20的结果!现在我把问题简化了:set @s="select top 20 @uid=uid from table"
exec sp_executesql @s,N'@uid varchar(100) output',@uid output真抱歉,我的表达能力有限,呵呵.我是想得到前20个用户的UID,该怎么办?
insert #t exec sp_executesql @s,N'@uid varchar(100) output',@uid output
select * from #t为什么都是空的!?
create table #t(uid varchar(100))insert #t exec(N'select top 10 uid from table')
select * from #t
drop table #t----这样可以得到结果集
-------------------proc2
create table #t(uid varchar(100))
Declare @s nvarchar(200),@uid varchar(20)set @s=N'select top 10 @uid=uid from table'
insert #t exec sp_executesql @s,N'@uid varchar(20) output',@uid output
select * from #t
drop table #t
--------为什么这样不可以呢?
declare @Sql varchar(800)
declare @str varchar(8000)
set @str='insert into #t(uid) '
set @Sql = ' SELECT top 20 uid FROM table
set @str=@str+@Sql
exec(@str)
--用游标declare tb cursor local
for
select top 20 uid from table
open tb
fetch tb into @uid
while @@fetch_stauts=0
begin
--- 处理
fetch tb into @uid
end
close tb
deallocate tb