Question

sqlserver存储过程加变量的问题

表：
create proc createTable
(@name varchar(50))
as
begin
if object_id(@name) is null
exec('create table '+ @name+ '(id int identity(1,1) primary key,name varchar(50) not null,manHour decimal not null,workTime datetime not null,wages decimal not null,pay decimal,paytime datetime,insertTime datetime not null)')
else
print -1
end
插入
create proc insertTableNO(@name varchar(50),@manHour decimal,@workTime datetime,@wages decimal,@insertTime datetime,@tableName varchar(50))
as
begin
if object_id(@tableName) is not null
exec('insert into '+ @tablename+ ' ( name,manHour,workTime,wages,insertTime) values('+@name+','+@manHour+','+@workTime+','+@wages+','+@insertTime+')')
else
return -1
end

测试语句：exec insertTableNo 'zlm',8,0,200,0,'employee1'
报错：在此上下文中不允许使用 'zlm'。此处只允许使用常量、表达式或变量。不允许使用列名。
谁帮我看看。哪写错了。

Answer 1

加上引号

create proc insertTableNO(
@name varchar(50),
@manHour decimal,
@workTime datetime,
@wages decimal,
@insertTime datetime,
@tableName varchar(50)
)
as
begin
if object_id(@tableName) is not null
exec('insert into '+ @tablename+ ' ( name,manHour,workTime,wages,insertTime)
values('''+@name+''','''+@manHour+''','''+@workTime+''','''+@wages+''','''+@insertTime+''')')
else
return -1
end