Sqlserver用存储过程创建临时表
Create Procedure [dbo].[Authority_Admin]
as
begin
--初始化浏览记录
create table #Tmp --创建临时表#Tmp
(
ID int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1
cid int,
cname varchar (200),
username NVARCHAR(MAX)
primary key (ID) --定义ID为临时表#Tmp的主键
);
insert into #Tmp(cid,cname)
select a.Content_topic_id, b.Content_topic_name + '-' + a.Content_topic_name from Ewk_Content_Topic a
inner join Ewk_Content_Topic b on a.Content_topic_uid = b.Content_topic_id
where a.Content_topic_uid in (750,784,766,986,993,1028,786) order by a.Content_topic_uid asc
Declare @cid int --栏目ID
Declare @username NVARCHAR(MAX)
Declare @Str NVARCHAR(MAX) --用来存放查询语句
Declare @Count int --求出总记录数
Declare @i int
Set @i = 0
Select @Count = Count(1) from #Tmp
While @i < @Count
Begin
if @i > 0
begin
select @cid = cid from #Tmp where id = @i
SELECT @username = STUFF((SELECT ','+ RealName+'('+Permission+')' FROM Authority a
inner join EwkCms_Admin as b on a.User_id=b.Id
WHERE a.Content_topic_id=@cid
for xml path('') ),1,1,'')
update #Tmp set username = @username where id = @i
end
Set @i = @i + 1
End
select * from #Tmp
drop table #Tmp
end