select 0 as id, count(*) as sql from table1 where type=@type union all select 1,count(*) as sql from table2 where type=@type union all select 2,count(*) as sql from table3 where type=@type
victorycyz(中海) 的好像不行也
--查询处理(因为你的记录数不确定,所以得用游标保险) declare @type int set @type=100declare @id int,@s nvarchar(4000),@re int create table #t(id int,SQL int)declare #t cursor local for select id,'select @re=('+SQL+')' from tb open #t fetch #t into @id,@s while @@fetch_status=0 begin exec sp_executesql @s ,N'@re int out ,@type int' ,@re out ,@type insert #t select @id,@re fetch #t into @id,@s end close #t deallocate #t select * from #t drop table #t
你的每条记录都是一个SQL语句取值,不慢也得慢.
exec sp_executesql @s ,N'@re int out ,@type int' ,@re out ,@type insert #t select @id,@re fetch #t into @id,@s 这里没看懂,运行结果也不对
--测试--测试数据 create table tb(Id int,SQL varchar(1000)) insert tb select 0,'select count(*) from master..sysobjects where xType = @Type' union all select 1,'select count(*) from pubs..sysobjects where xType = @Type' union all select 2,'select count(*) from northwind..sysobjects where xType = @Type' go--查询处理(因为你的记录数不确定,所以得用游标保险) declare @type varchar(10) set @type='U'declare @id int,@s nvarchar(4000),@re int create table #t(id int,SQL int)declare #t cursor local for select id,'select @re=('+SQL+')' from tb open #t fetch #t into @id,@s while @@fetch_status=0 begin exec sp_executesql @s ,N'@re int out ,@type varchar(10)' ,@re out ,@type insert #t select @id,@re fetch #t into @id,@s end close #t deallocate #t--显示结果 select * from #t drop table #t--显示直接执行查询语句进行查询的结果 select id=0,SQL=(select count(*) from master..sysobjects where xType = @Type) union all select id=0,SQL=(select count(*) from pubs..sysobjects where xType = @Type) union all select id=0,SQL=(select count(*) from northwind..sysobjects where xType = @Type) go--删除测试 drop table tb/*--测试结果(有什么不对??)id SQL ----------- ----------- 0 10 1 12 2 13(所影响的行数为 3 行)id SQL ----------- ----------- 0 10 0 12 0 13(所影响的行数为 3 行) --*/
select 0 as id, count(*) as sql from table1 where type=@type
union all
select 1,count(*) as sql from table2 where type=@type
union all
select 2,count(*) as sql from table3 where type=@type
declare @type int
set @type=100declare @id int,@s nvarchar(4000),@re int
create table #t(id int,SQL int)declare #t cursor local for
select id,'select @re=('+SQL+')' from tb
open #t
fetch #t into @id,@s
while @@fetch_status=0
begin
exec sp_executesql @s
,N'@re int out
,@type int'
,@re out
,@type
insert #t select @id,@re
fetch #t into @id,@s
end
close #t
deallocate #t
select * from #t
drop table #t
,N'@re int out
,@type int'
,@re out
,@type
insert #t select @id,@re
fetch #t into @id,@s
这里没看懂,运行结果也不对
create table tb(Id int,SQL varchar(1000))
insert tb select 0,'select count(*) from master..sysobjects where xType = @Type'
union all select 1,'select count(*) from pubs..sysobjects where xType = @Type'
union all select 2,'select count(*) from northwind..sysobjects where xType = @Type'
go--查询处理(因为你的记录数不确定,所以得用游标保险)
declare @type varchar(10)
set @type='U'declare @id int,@s nvarchar(4000),@re int
create table #t(id int,SQL int)declare #t cursor local for
select id,'select @re=('+SQL+')' from tb
open #t
fetch #t into @id,@s
while @@fetch_status=0
begin
exec sp_executesql @s
,N'@re int out
,@type varchar(10)'
,@re out
,@type
insert #t select @id,@re
fetch #t into @id,@s
end
close #t
deallocate #t--显示结果
select * from #t
drop table #t--显示直接执行查询语句进行查询的结果
select id=0,SQL=(select count(*) from master..sysobjects where xType = @Type)
union all
select id=0,SQL=(select count(*) from pubs..sysobjects where xType = @Type)
union all
select id=0,SQL=(select count(*) from northwind..sysobjects where xType = @Type)
go--删除测试
drop table tb/*--测试结果(有什么不对??)id SQL
----------- -----------
0 10
1 12
2 13(所影响的行数为 3 行)id SQL
----------- -----------
0 10
0 12
0 13(所影响的行数为 3 行)
--*/