在动态SQL中，“未封闭的引号"和“'"附近的“语法不正确" [英] In dynamic SQL "Unclosed quotation mark" and 'incorrect syntax near '"

问题描述

我试图检查记录是否存在，如果不存在则插入；我想从单个服务器而不是单个数据库运行脚本.一个人指导我起草了以下内容.

I was trying to check if a record exists and insert if not exist; I would like to run the script from a single server, instead of the individual databases. A folk guided me with drafting the following.

DECLARE @dbname varchar(20)
DECLARE @sql nvarchar(max)

SET @dbname = 'database_name' 

SET @sql = 'IF NOT EXISTS (select 1 from ' + @dbname+ '.dbo.tablename WHERE parameter like ''%part_of_record_name%'')'''
EXEC sp_executesql @sql 

 BEGIN  
 PRINT 1 
 END
 ELSE 
 BEGIN
 PRINT 0 
 END

但是，这是打印:字符串后的未引号."

However, this is printing: "Unclosed quotation mark after the character string ''."

我尝试过这个:

SET @sqlstring = 'IF NOT EXISTS (select 1 from ' + @dbname+ '.dbo.tablename WHERE parameter like ''%part_of_record_name%'')'

它还会显示一条错误消息')'附近的语法不正确."

It also displays an error message "Incorrect syntax near ')'."

任何解决此问题的指南都值得赞赏.谢谢！

Any guide in resolving this is appreciated much. Thanks!

推荐答案

最后加逗号，整个内容也应该是动态sql

extra comma at the end, also the whole thing should be dynamic sql

DECLARE @dbname varchar(20)
DECLARE @sql nvarchar(max)
SET @dbname = QUOTENAME('database_name')

SET @sql = 'IF NOT EXISTS (select 1 from ' + @dbname+ '.dbo.tablename WHERE parameter like ''%part_of_record_name%'')
 BEGIN  
 PRINT 1 
 END
 ELSE 
 BEGIN
 PRINT 0 
 END 
'
EXEC sp_executesql @sql 

这篇关于在动态SQL中，“未封闭的引号"和“'"附近的“语法不正确"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！