“筑巢”动态 SQL 中的变量 - 如何保持有效的语法？
如果有人问这个问题，我很抱歉，而且我太笨了，无法找到答案。
我试图确定执行以下操作的最佳方法：
Declare @sql nvarchar(4000)
, @Filter nvarchar(500)
SET @filter = 'WHERE value1 IN (''A'',''B'',''C'')'
SET @sql = '
DECLARE @Field nvarchar(50)
DECLARE process CURSOR FOR
SELECT value2
FROM table
'+@filter+'
OPEN process
FETCH NEXT FROM process
INTO @field
WHILE @@Fetch_Status = 0
BEGIN
EXEC stored_procedure
@field = @field
@filter = '+@filter+'
FETCH NEXT FROM process
INTO @field
END
CLOSE process
DEALLOCATE process
' print @sql execute sp_executesql @sql
这里的问题是它转换为
DECLARE @Field nvarchar(50)
DECLARE process CURSOR FOR
SELECT value2
FROM table
WHERE value1 IN ('A','B','C')
OPEN process
FETCH NEXT FROM process
INTO @field
WHILE @@Fetch_Status = 0
BEGIN
EXEC stored_procedure
@field = 'N'
@filter = 'WHERE value1 IN ('A','B','C')'
FETCH NEXT FROM process
INTO @field
END
CLOSE process
DEALLOCATE process
当然，这会导致 A、B 和 C 处出现语法错误
我尝试了 replace( 的多种变体@filter,','') 来纠正这个问题，但这似乎对我没有任何好处。
请问有什么想法吗？
I apologize if this has been asked and I am to dense to grok the answer.
I am trying to determine the best way to do the following:
Declare @sql nvarchar(4000)
, @Filter nvarchar(500)
SET @filter = 'WHERE value1 IN (''A'',''B'',''C'')'
SET @sql = '
DECLARE @Field nvarchar(50)
DECLARE process CURSOR FOR
SELECT value2
FROM table
'+@filter+'
OPEN process
FETCH NEXT FROM process
INTO @field
WHILE @@Fetch_Status = 0
BEGIN
EXEC stored_procedure
@field = @field
@filter = '+@filter+'
FETCH NEXT FROM process
INTO @field
END
CLOSE process
DEALLOCATE process
' print @sql execute sp_executesql @sql
The problem here is that it translates to
DECLARE @Field nvarchar(50)
DECLARE process CURSOR FOR
SELECT value2
FROM table
WHERE value1 IN ('A','B','C')
OPEN process
FETCH NEXT FROM process
INTO @field
WHILE @@Fetch_Status = 0
BEGIN
EXEC stored_procedure
@field = 'N'
@filter = 'WHERE value1 IN ('A','B','C')'
FETCH NEXT FROM process
INTO @field
END
CLOSE process
DEALLOCATE process
Of course in this leads to a syntax error at A, B, and C
I have tried a number of variations of replace(@filter,','') to correct this, but this doesn't seem to do me any good.
Ideas please?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
使用：
Use:
改变这部分
像这样
Change this part
like this