我无法在SQL Server中插入表(I can't insert into table in SQL Server)

我无法在SQL Server中插入表(I can't insert into table in SQL Server)

我创建了一个存储过程，它有两个语句insert和update 。

它有两个参数：

@content as nvarchar(10), @fieldName as int

在Update行中，一切正常，但它不会插入表。 语法和一切都是真的，但是当我执行时

Execute sp_update 432,4

SQL 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 int

In 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,4

SQL 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 + ''''