我创建了一个存储过程,它有两个语句insert和update 。
它有两个参数:
@content as nvarchar(10), @fieldName as int在Update行中,一切正常,但它不会插入表。 语法和一切都是真的,但是当我执行时
Execute sp_update 432,4SQL Server显示此错误:
Msg 207,Level 16,State 1,Line 1 列名称“up”无效。 Msg 207,Level 16,State 1,Line 1 列名称“Field3”无效。
这是我的脚本:
ALTER procedure [dbo].[sp_update] @content as nvarchar(10), @fieldName as int as declare @ff as varchar set @ff = Convert(varchar,@fieldName) declare @f as char(7) set @f = 'Field'+ @ff declare @sqlupdate varchar(500) declare @sqlinserttoChangelog varchar(500) set @sqlinserttoChangelog = 'Insert Into dbo.changelog (changeType, fieldname) Values ('+'up'+','+@f+')' Exec (@sqlinserttoChangelog) set @sqlupdate = 'update TableTest set ' + @f + ' = '+@Content Exec (@sqlupdate)I created a stored procedure which has two statements insert and update.
It gets two parameters:
@content as nvarchar(10), @fieldName as intIn Update line everything works correctly, but it doesn't insert to table. Syntax and everything is true but when I execute
Execute sp_update 432,4SQL Server shows this error:
Msg 207, Level 16, State 1, Line 1 Invalid column name 'up'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Field3'.
This is my script:
ALTER procedure [dbo].[sp_update] @content as nvarchar(10), @fieldName as int as declare @ff as varchar set @ff = Convert(varchar,@fieldName) declare @f as char(7) set @f = 'Field'+ @ff declare @sqlupdate varchar(500) declare @sqlinserttoChangelog varchar(500) set @sqlinserttoChangelog = 'Insert Into dbo.changelog (changeType, fieldname) Values ('+'up'+','+@f+')' Exec (@sqlinserttoChangelog) set @sqlupdate = 'update TableTest set ' + @f + ' = '+@Content Exec (@sqlupdate)最满意答案
由于您正在创建动态sql ,因此值应使用单引号进行换行。 要逃避单引号,必须加倍,例如。
set @sqlinserttoChangelog = 'Insert Into dbo.changelog (changeType, fieldname) Values ('''+'up'+''', '+@f+')'与content相同
set @sqlupdate = 'update TableTest set ' + @f + ' = '''+ @Content + ''''Since you are creating dynamic sql, the values should be wrap with single quote. To escape single quote, it must be doubled, eg.
set @sqlinserttoChangelog = 'Insert Into dbo.changelog (changeType, fieldname) Values ('''+'up'+''', '+@f+')'same with the content
set @sqlupdate = 'update TableTest set ' + @f + ' = '''+ @Content + ''''更多推荐
发布评论