CREATE procedure dep_huif
(@dep nvarchar(200),
@Mendian nvarchar(200),
@startdate nvarchar(200),
@enddate nvarchar(200),
@all varchar(20),
@slist varchar(200))
AS
exec('select ts_name,ts_id into ##tmp_huifsubtype from ts_selections a where a.ts_id in ('+@slist+')')
if @all=1
begin
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into ##tmp_huif from usr_incidents where (TS_workflow_type =5311) and ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
end
if @all=2
begin
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into ##tmp_huif from usr_incidents where (TS_SUBDEP ='+@dep+') AND ts_workflow_type=5311 and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
end
if @all=3
begin
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into ##tmp_huif from usr_incidents where (TS_SUBDEP ='+@dep+') AND ts_workflow_type=5311 and ts_mendian='+@Mendian+' and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
end

declare @s varchar(8000)
set @s=''
select
@s=@s+',"'+b.ts_name+'|完成" =sum(case when ts_hfsubtype='+rtrim(b.ts_ID)+' and ts_activeinactive=1 then 1 else 0 end)'
+',"'+b.ts_name+'|未完成"=sum(case when ts_hfsubtype='+rtrim(b.ts_ID)+' and ts_activeinactive=0 then 1 else 0 end)'
from ##tmp_huifsubtype b
set @s='select '+stuff(@s,1,1,'')+' from ##tmp_huif '
print @s
exec(@s)
drop table ##tmp_huifsubtype,##tmp_huif
GO

我想将以上存储过程中的临时表名，换成随机临时表名，但是SQL字符串凑不出来，请高手指点

='tmp_huifsubtype'+cast(@i as nvarchar(200))
set @tablename1='tmp_huif'+cast(@i as nvarchar(200))
set @droptable='drop table '+@tablename
set @droptable1='drop table '+@tablename1
set @sqlstr='select ts_name,ts_id into '+@tablename+' from ts_selections a where a.ts_id in ('+@slist+')'
exec(@sqlstr)
--exec('select ts_name,ts_id into ##tmp_huifsubtype from ts_selections a where a.ts_id in ('+@slist+')')
if @all=1
begin
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into '+@tablename1+' from usr_incidents where (TS_workflow_type =5311) and ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
end
if @all=2
begin
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into '+@tablename1+' from usr_incidents where (TS_SUBDEP ='+@dep+') AND ts_workflow_type=5311 and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
end
if @all=3
begin
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into '+@tablename1+' from usr_incidents where (TS_SUBDEP ='+@dep+') AND ts_workflow_type=5311 and ts_mendian='+@Mendian+' and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
end

declare @aa varchar(8000),@sql_str varchar(8000)
--set @s=''
set @aa='declare @s varchar(8000),@sql_str varchar(8000) '+'set @s='''''+
' set @sql_str=''select''+
@s+''=''+@s+''''+'''',"''''+b.ts_name+''''|完成" =sum(case when ts_hfsubtype=''''+rtrim(b.ts_ID)+'''' and ts_activeinactive=1 then 1 else 0 end)''''
+'''',"''''+b.ts_name+''''|未完成"=sum(case when ts_hfsubtype=''''+rtrim(b.ts_ID)+'''' and ts_activeinactive=0 then 1 else 0 end)''''
from '''+@tablename+''' b '
declare @exes varchar(200)
set @exes=' exec(@s)'

set @aa=@aa+'exec(@sql_str) '+'set @s=''select ''+stuff(@s,1,1,'''')+'' from ''+@tablename1'+' exec(@s)'

print @aa
exec(@aa)

exec(@droptable)
exec(@droptable1)
GO