存储过程中随机临时表名问题

[entironment]

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

[hellowork]

可以使用newid()来生成随机临时表名称,例如:
declare @tablename sysname
set @tablename = replace(cast(newid() as sysname),'-','')
exec('select top 10 id = identity(int,1,1) into #' + @tablename + ' from syscolumns
select * from #' + @tablename + '
drop table #' + @tablename)

[entironment]

临时表名生成后，存在变量里，这个存储过程后面有个动态SQL语句，要使用到这个表
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)
主要是这里凑字符串，该怎么凑，该怎么把动态生成的临时表明传进去

[hellowork]

楼主上面的代码看起来有些太乱了,能不能把上面的代码简化一下,少写些字段和条件,让脉络看起来清晰一些.
另外,
set @aa=@aa+'exec(@sql_str) '+'set @s=''select ''+stuff(@s,1,1,'''')+'' from ''+@tablename1'+' exec(@s)'
这个@tablename1是不是手误?应该是@tablename吧.

[entironment]

declare @str1 varchar(200),@str2 varchar(200),@str varchar(200)
set @str='set str1= secect * from @a1'+' exec (@str)'
exec(@str)
就是说实现这样的功能，这里的@a1用几个‘号

[paoluo]

from '''+@tablename+''' b '

這裡應該為

from '+@tablename+ ' b '

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


應該為

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

不過，你組合的語句裡面有點混亂。

[hellowork]

declare @str1 varchar(200),@str2 varchar(200),@str varchar(200) ,@a1 sysname
set @a1 = 'table'
set @str='set str1= ''select * from ' + @a1 + ' exec (@str)'''
print @str /*请查看一下字符串构建的结果*/

[hellowork]

请楼主这样试试:
CREATE procedure dep_huif
(@dep nvarchar(200),
@Mendian nvarchar(200),
@startdate nvarchar(200),
@enddate nvarchar(200),
@all varchar(20),
@slist varchar(200))
AS
declare @tablename sysname
declare @tablename_huifsubtype sysname
----生成随机临时表名称
set @tablename = '##' + replace(cast(newid() as sysname),'-','')
set @tablename_huifsubtype = '##' + replace(cast(newid() as sysname),'-','')
----生成随机临时表
exec('select ts_name,ts_id into ' + @tablename_huifsubtype + ' from ts_selections a where a.ts_id in ('+@slist+')')
if @all=1
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into ' + @tablename + ' from usr_incidents where (TS_workflow_type =5311) and ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
if @all=2
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into ' + @tablename + ' from usr_incidents where (TS_SUBDEP ='+@dep+') AND ts_workflow_type=5311 and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')
if @all=3
exec('select ts_hftype,ts_hfsubtype,ts_activeinactive into ' + @tablename + ' from usr_incidents where (TS_SUBDEP ='+@dep+') AND ts_workflow_type=5311 and ts_mendian='+@Mendian+' and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''')

declare @sql nvarchar(4000)
declare @s varchar(8000)
set @s=''
----构建汇总字符串
set @sql =
'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_huifsubtype + ' b '
----获得汇总的字符串
exec sp_executesql @sql,N'@s varchar(8000) output',@s output
----执行汇总
set @s='select '+ stuff(@s,1,1,'') + ' from ' + @tablename
print @s
EXEC(@s)

----删除临时表
exec ('drop table ' + @tablename + ',' + @tablename_huifsubtype)
GO

[entironment]

谢谢各位
结贴，给分

[entironment]

hellowork(一两清风)
用NEWID()生成的临时表在那里看数据啊
另外想问一下sp_executesql什么作用
谢谢

[OracleRoob]

动态sql语句基本语法
1 :普通SQL语句可以用Exec执行

eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL

eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格

当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名

declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错



declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确

3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中？

declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num

[net3]

sp_executesql是执行带参数的存储过程

A. 执行简单的 SELECT 语句
下面的示例创建并执行一个简单的 SELECT 语句，其中包含名为 @level 的嵌入参数。

execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35

B. 执行动态生成的字符串
下面的示例显示使用 sp_executesql 执行动态生成的字符串。该示例中的存储过程用来向一组表中插入数据，该表用于划分一年的销售数据。一年中的每个月均有一个表，格式如下：

CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)

[net3]

不好意思，没贴全，建议楼主去看看sqlserver的帮助