请问存储过程中表名怎么样用变量来替代??

[xiaoyuehen]

if object_id('dbo.c_dq') is not null
drop procedure dbo.c_dq
go

CREATE PROCEDURE dbo.c_dq
@ssdq varchar(20) = null
AS
declare @mydq nvarchar(20)
declare @temp_tabname nvarchar(1000)
declare @temp_msdm nvarchar(20)
declare @exec_str nvarchar(2000)
DEALLOCATE dq_cursor
declare dq_cursor scroll cursor for select msdm from hcszhmc where lsdq='tb'
open dq_cursor
FETCH first from dq_cursor into @temp_msdm
if @@FETCH_STATUS=0
begin
declare @temp_datas table
(id bigint identity(1,1) not null,
lsdq nvarchar(20) not null,
msdm nvarchar(20) not null,
rq smalldatetime not null,
je money default(0))

while @@FETCH_STATUS =0
begin
FETCH NEXT FROM dq_cursor INTO @temp_msdm
set @temp_tabname=rtrim(@temp_msdm)+'..hrc21'
insert @temp_datas (lsdq,msdm,rq,je) select @ssdq,ddm,rq,je from @temp_tabname
end
end
else
print '.............................'
close dq_cursor
DEALLOCATE dq_cursor
select * from @temp_datas
GO

/*********************
insert @temp_datas (lsdq,msdm,rq,je) select @ssdq,ddm,rq,je from @temp_tabname
出错:
服务器: 消息 137，级别 15，状态 2，过程 c_dq，行 27
必须声明变量 '@temp_tabname'。

@temp_tabname若变成temp_tabname则存储过程可以被建立,当然执行的时候会出错,因为库中没有此表...:(

@temp_datas是存储过程中建立的临时存储记录集...

若我改成exec 'insert '+@temp_datas+' (lsdq,msdm,rq,je) select '+@ssdq+',ddm,rq,je from '+@temp_tabname
则出现如下错误:
服务器: 消息 137，级别 15，状态 2，过程 c_dq，行 26
必须声明变量 '@temp_datas'。

刚开始学存储过程...这个问题怎么解决呢?
需要建多个过程吗?

[pengdali]

if object_id('dbo.c_dq') is not null
drop procedure dbo.c_dq
go

CREATE PROCEDURE dbo.c_dq
@ssdq varchar(20) = null
AS
declare @mydq nvarchar(20)
declare @temp_tabname nvarchar(1000)
declare @temp_msdm nvarchar(20)
declare @exec_str nvarchar(2000)
DEALLOCATE dq_cursor
declare dq_cursor scroll cursor for select msdm from hcszhmc where lsdq='tb'
open dq_cursor
FETCH first from dq_cursor into @temp_msdm
if @@FETCH_STATUS=0
begin
create table #temp_datas
(id bigint identity(1,1) not null,
lsdq nvarchar(20) not null,
msdm nvarchar(20) not null,
rq smalldatetime not null,
je money default(0))

while @@FETCH_STATUS =0
begin
FETCH NEXT FROM dq_cursor INTO @temp_msdm
set @temp_tabname=rtrim(@temp_msdm)+'..hrc21'
exec('insert #temp_datas (lsdq,msdm,rq,je) select '+@ssdq+',ddm,rq,je from '+@temp_tabname)
end
end
else
print '.............................'
close dq_cursor
DEALLOCATE dq_cursor
select * from #temp_datas
GO

[pengdali]

如果上面不是你的意思那你改为：

if object_id('dbo.c_dq') is not null
drop procedure dbo.c_dq
go

CREATE PROCEDURE dbo.c_dq
@ssdq varchar(20) = null
AS
declare @mydq nvarchar(20)
declare @temp_tabname nvarchar(1000)
declare @temp_msdm nvarchar(20)
declare @exec_str nvarchar(2000)
DEALLOCATE dq_cursor
declare dq_cursor scroll cursor for select msdm from hcszhmc where lsdq='tb'
open dq_cursor
FETCH first from dq_cursor into @temp_msdm
if @@FETCH_STATUS=0
begin
create table #temp_datas
(id bigint identity(1,1) not null,
lsdq nvarchar(20) not null,
msdm nvarchar(20) not null,
rq smalldatetime not null,
je money default(0))

while @@FETCH_STATUS =0
begin
FETCH NEXT FROM dq_cursor INTO @temp_msdm
set @temp_tabname=rtrim(@temp_msdm)+'..hrc21'
exec('insert #temp_datas (lsdq,msdm,rq,je) select '''+@ssdq+''',ddm,rq,je from '+@temp_tabname)
end
end
else
print '.............................'
close dq_cursor
DEALLOCATE dq_cursor
select * from #temp_datas
GO

[xiaoyuehen]

感谢所有朋友的回复,谢谢,,问题已差不多解决了...:)

用的是大力兄的建立区域临时表的方法,可以实现我的要求..

再次感谢朋友的热情帮助...

CREATE PROCEDURE dbo.c_dq
@ssdq varchar(20) = null
,@inputdate smalldatetime =null
,@sele_string nvarchar(512)
AS
/*建立临时表*/
create table #temptabcount
(id bigint identity(1,1) not null,
lsdq nvarchar(20) not null,
msdm nvarchar(20) not null,
rq smalldatetime not null,
je money default(0))
declare @temp_tabname nvarchar(1000)
/*声明用于提取各分店库名的变量*/
declare @temp_msdm nvarchar(50)
/*用于提取各分店名*/
declare @temp_lsdq nvarchar(50)
/*用于提取各分店名*/

/*定义可自由滚动的游标,取出地区下所有分店代码*/
declare dq_cursor scroll cursor for select msdm,lsdq from hcszhmc where lsdq=@ssdq
/*打开记录集*/
open dq_cursor
FETCH first from dq_cursor into @temp_msdm,@temp_lsdq
if @@FETCH_STATUS=0
begin
while @@FETCH_STATUS =0
begin
FETCH NEXT FROM dq_cursor INTO @temp_msdm,@temp_lsdq
set @temp_tabname=rtrim(@temp_msdm)+'..hrc21'
--print @temp_tabname
exec('insert #temptabcount (lsdq,msdm,rq,je) select '''+@temp_lsdq+''' as lsdq,ddm,rq,je from '+@temp_tabname+' where rq>'''+@inputdate+'''')
end
end
close dq_cursor
DEALLOCATE dq_cursor
exec(@sele_string)
--select * from #temptabcount order by rq desc
GO
c_dq 'tld','2003-5-6','select * from #temptabcount order by rq desc'

[caiyunxia]

exec ('insert '+ @temp_datas + '(lsdq,msdm,rq,je) select '+ @ssdq +',ddm,rq,je from '+@temp_tabname)
用动态SQL

[firetoucher]

up
用动态sql语句，

[xiaoyuehen]

若我改成exec 'insert '+@temp_datas+' (lsdq,msdm,rq,je) select '+@ssdq+',ddm,rq,je from '+@temp_tabname
则出现如下错误:
服务器: 消息 137，级别 15，状态 2，过程 c_dq，行 26
必须声明变量 '@temp_datas'。

动态SQL怎么用?

[CrazyFor]

表名不能使用变量.

用动态SQL,如 caiyunxia(monkey) 所说那样.