In dynamic SQL "Unclosed quotation mark" and 'incorrect syntax near '"

Question

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 ''."

I tried it with this:

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!

Answer 1

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