选择和更新存储过程中的语句

Question

我正在尝试先用 select 语句编写一个存储过程，如果该语句有效，则运行更新语句。示例代码如下，其中 table1 有两列TableName&ColumnName。如果有从table1返回的记录，则运行 update 语句，其中表名和列取自table1结果：

DECLARE @tablename NVARCHAR(100), 
@columnname NVARCHAR(100), 
@newid INT, 
@sql NVARCHAR(500), 
@Id INT,  
@name NVARCHAR(50), 
@result NVARCHAR(100)
SET @newid = 0
SET @Id = 1
SET @name = 'aa'
SET @result = 'test'
SELECT 
@newid = Id, @tablename = TableName, @columnname = ColumnName 
FROM
Table1 
WHERE
Id = @Id
IF(@newid > 0)
BEGIN
SET @sql = 'UPDATE ' + @tablename + ' SET ' + @columnname + ' = ' + @result + 'where name = ' +@name
END

但是，该脚本不起作用。谁能帮忙？谢谢

Answer 1

您的查询应如下所示

DECLARE @tablename NVARCHAR(100), 
@columnname NVARCHAR(100), 
@newid INT, 
@sql NVARCHAR(500), 
@Id INT,  
@name NVARCHAR(50), 
@result NVARCHAR(100)
SET @newid = 0
SET @Id = 1
SET @name = 'aa'
SET @result = 'test'
SELECT 
@newid = Id, @tablename = TableName, @columnname = ColumnName 
FROM
Table1 
WHERE
Id = @Id
IF(@newid > 0)
BEGIN
SET @sql = 'UPDATE ' + @tablename + ' SET ' + @columnname + ' = ' + @result + 'where name = ' +@name
exec sp_executesql @sql
END

Answer 2

向硬编码文本添加单引号。

set @sql = 'UPDATE ' + @tablename + ' SET ' + @columnname + ' = ''' + @result + ''' where name = ''' +@name + ''''

请记住在执行动态 SQL 之前打印它以检查错误。

PRINT (@sql)

Answer 3

执行查询。

使用执行尝试此代码。

Create  PROCEDURE [dbo].[procedure_name]
@tablename NVARCHAR(100), 
@columnname NVARCHAR(100), 
@newid INT, 
@sql NVARCHAR(500), 
@Id INT,  
@name NVARCHAR(50), 
@result NVARCHAR(100)
AS
SET NOCOUNT ON

SET @newid = 0
SET @Id = 1
SET @name = 'aa'
SET @result = 'test'
SELECT 
@newid = 1, @tablename = 'abc', @columnname = 'b' 
WHERE
1 = @Id
IF(@newid > 0)
BEGIN
SET @sql = 'UPDATE ' + @tablename + ' SET ' + @columnname + ' = ''' + @result + ''' where name = ''' +@name+''''
PRINT @sql
EXEC @sql
END