SQL Server存储过程使用动态表名插入语句 [英] SQL Server stored procedure insert statement using dynamic table name

问题描述

任何帮助请欣赏，

因为我希望使用动态变量表进入sql存储过程。

我的代码如下。

any help please appreciated,
as i am looking to work with dynamic variable table into sql stored procedure.
my code is below.

ALTER procedure [dbo].[emailListUpdate]
(
@EmailID as varchar(120),
@ListID as varchar(120)
)
as
declare 
@tablename as varchar(120)='EmailList'+@ListID
begin
set nocount on
if not exists (select top 1 * from [@tablename] where EmailID=@EmailID)
begin
insert into [@tablename](EmailID) values(@EmailID)
select result=2 --New List Inserted
end
else
select result=1--already exist with diffrent status update and return old status with id
set nocount off
end

exec dbo.emailListUpdate 219809,29

以这种方式运行我正在接受



消息208，级别16，状态1，过程emailListUpdate，第12行

无效的对象名称'dbo。@ tablename'。

exec dbo.emailListUpdate 219809,29
while running in such way i am gettting

Msg 208, Level 16, State 1, Procedure emailListUpdate, Line 12
Invalid object name 'dbo.@tablename'.

推荐答案

DECLARE @SQL VARCHAR(MAX);

SET @SQL ='if not exists (select top 1 * from ['+@tablename+'] where EmailID='+@EmailID+')
begin
insert into ['+@tablename+'](EmailID) values('+@EmailID+')
select result=2
end
else
select result=1
'
EXEC (@SQL);

这是找到的解决方案。

This is the solution found.

这篇关于SQL Server存储过程使用动态表名插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！