问题描述
如果我们包含 AND HostApplication 子句,此查询将不起作用.
This query will not work if we include the AND HostApplication clause.
参数:
ALTER PROCEDURE [dbo].[updateNumbers_ArchiveDB] ( @accountNumber varchar(50), @accountType char(4), @padding varchar(50), @proc_dateStart datetime, @proc_dateEnd datetime )
这是动态 SQL:
set @q = 'Update ' + @cTableName + ' SET LogicalAccount = '+ @padding + @accountNumber + ' WHERE ProcessDate BETWEEN CAST('''+CONVERT(VARCHAR(20),@proc_dateStart)+''' AS DATE) AND CAST('''+CONVERT(VARCHAR(20),@proc_dateEnd)+''' AS DATE)' + ' AND HostApplication =' + '''+ @accountType +'''
这是我的执行声明:
exec updateNumbers_ArchiveDB @accountNumber = N'1020', @accountType = N'd', @padding = N'123', @proc_dateStart = '2014-01-30', @proc_dateEnd = '2014-01-31'
如果我删除 @accounttype 周围的单引号:(''+@accounttype +'') 我得到这个错误:
If I remove a single quote around @accounttype: (''+@accounttype +'') I get this error:
消息 207,级别 16,状态 1,第 1 行
无效的列名d".
Msg 207, Level 16, State 1, Line 1
Invalid column name 'd'.
使用 3 个引号,它可以正常运行而不会出错,但不会进行任何更新.
With 3 quotes it works runs without error, but no updates are made.
使用 1 个引号 (('+@accounttype +')) 我得到:
With 1 quote (('+@accounttype +')) I get:
消息 137,级别 15,状态 2,第 1 行
必须声明标量变量@accountType".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@accountType".
我意识到在动态 SQL 中使用引号是一门艺术,但我没有找到任何关于在动态 SQL 中使用 char 数据类型的资源.
I realize there is an art to using quotes in dynamic SQL, but I have not found any resources on using the char data type in dynamic SQL.
推荐答案
+ ''''+ @accountType +''''
应该修复它