Create proc testfn @counts int
as
begin
set nocount on
create table #temptable(storeid int,storename varchar(50))
declare @str varchar(1000)
set @str = 'insert into temptable '+
'select top '+cast(@counts as varchar(10))+
' * from stores'
exec (@str)
select * from #temptable drop table #temptable
end
as
begin
set nocount on
create table #temptable(storeid int,storename varchar(50))
declare @str varchar(1000)
set @str = 'insert into temptable '+
'select top '+cast(@counts as varchar(10))+
' * from stores'
exec (@str)
select * from #temptable drop table #temptable
end
例如:
select * from dbo.testfn(10)
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
CONSTRAINT [PK_tabChar] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GOinsert into tabChar
select 'aaaaa'
union select 'bbbbb'
union select 'ccccc'
union select 'ddddd'
union select 'eeeee'
union select 'fffff'
union select 'gggggg'
union select 'hhhhhh'
go
Create proc pAA(@counts int)
as
begin
set nocount on
create table #temptable(id int identity(1,1),storename varchar(50))
declare @str varchar(1000)
set @str = 'insert into #temptable '+
'select top '+cast(@counts as varchar(10))+
' name from tabChar'
exec (@str)
select * from #temptable drop table #temptable
endgoexec pAA 5
-----------------------------------------------------
1 aaaaa
2 bbbbb
3 ccccc
4 ddddd
5 eeeee
1.建臨時表或表變量
2.insert into 臨時表或表變量
exec 存儲過程3.
select * from 臨時表或表變量關注高手的好方法
returns @temptable table(storeid int,storename varchar(50))
as
begin
declare @r table(id int identity(1,1),storeid int,storename varchar(50))
insert @r select * from stores
insert @temptable select storeid,storename
from @r where id<=@counts
return
end
go
Create function testfn(@counts int)
returns @temptable table(storeid int,storename varchar(50))
as
begin
insert @temptable select storeid,storename from stores a
where (select sum(1) from stores where storeid<=a.storeid)<=@counts
return
end
go
Create function testfn(@counts int)
returns @temptable table(storeid int,storename varchar(50))
as
begin
insert @temptable select storeid,storename
from stores a
where (select count(*)
from stores
where storeid<=a.storeid)<=@counts
return
end
go