create
table
bb(aid
int
,aname
char
(
1
),acom
char
(
1
))
--
------ 建测试表
go
insert bb -- -------------------插入记录
select 1 , ' a ' , ' d ' union all
select 2 , ' b ' , ' c ' union all
select 3 , ' k ' , ' s '
go
select *
from bb
go
create proc ReturnN( @n int ) -- -------------转入参数
as
begin -- -----------------------------------------------------------------------------------模块
declare @sql varchar ( 8000 ) -- ----------- SQL字符串
declare @b int -- -------CIRCLE参数
set @b = 0 -- ------------从0开始
set @sql = ''
while @b < @n -- --------<@n,输出多条
begin
set @sql = @sql + ' select * from bb where AID= ' + cast ( @n as varchar ( 10 )) + ' union all ' -- ---------加入 union all, 太妙了
set @b = @b + 1 -- -------- 累加
end
set @sql = substring ( @sql , 1 , len ( @sql ) - len ( ' union all ' )) -- -----------------加这个条件,为了去除语句字符串的最后union all
print @sql -- -------显式调试打印动态SQL语句
exec ( @sql ) -- -------- 一定要加入这 exec(@sql)
end -- --------------------------------------------------------------------------------------------模块
go
exec returnN 2 -- ------调用
-- ----------------------
aid aname acom
-- ---------------------
2 b c
2 b c
go
insert bb -- -------------------插入记录
select 1 , ' a ' , ' d ' union all
select 2 , ' b ' , ' c ' union all
select 3 , ' k ' , ' s '
go
select *
from bb
go
create proc ReturnN( @n int ) -- -------------转入参数
as
begin -- -----------------------------------------------------------------------------------模块
declare @sql varchar ( 8000 ) -- ----------- SQL字符串
declare @b int -- -------CIRCLE参数
set @b = 0 -- ------------从0开始
set @sql = ''
while @b < @n -- --------<@n,输出多条
begin
set @sql = @sql + ' select * from bb where AID= ' + cast ( @n as varchar ( 10 )) + ' union all ' -- ---------加入 union all, 太妙了
set @b = @b + 1 -- -------- 累加
end
set @sql = substring ( @sql , 1 , len ( @sql ) - len ( ' union all ' )) -- -----------------加这个条件,为了去除语句字符串的最后union all
print @sql -- -------显式调试打印动态SQL语句
exec ( @sql ) -- -------- 一定要加入这 exec(@sql)
end -- --------------------------------------------------------------------------------------------模块
go
exec returnN 2 -- ------调用
-- ----------------------
aid aname acom
-- ---------------------
2 b c
2 b c