Dynamic sql unclosed quote error

Question

I'm having trouble with a dynamic sql query that contains a parameter that includes an apostrophe.  I have an application that replaces all single quotes with double quotes.   I receive an unclosed quote error when the stored procedure is executed. Here is a sample of the text that is causing the error.

Application textbox value = 'I can't go home'

After the replace logic is applied = 'I can''t go home'

This value is passed to the @comments parameter of the stored procedure.

Stored procedure
delcare @d_sql varchar(max)
declare @comments varchar(max)
set @d_sql = @d_sql + insert into #tblComments select ''' + @comments + ''' '  --line causing error

Answer 1

Hi James,

Try like this replace all single quotes with 2 single  quotes instead of double quotes.

Try this link for different methods of handling quotes - http://social.msdn.microsoft.com/Forums/en/transactsql/thread/bbc61519-5136-4364-80fa-eff80efb3747

--CREATE  TABLE ##tblComments(comments varchar(max)) 
DECLARE @d_sql varchar(max),
        @comments varchar(max)= 'I can ''''t go home'
SET @d_sql = 'insert into ##tblComments select ('''+@comments +''')'
EXEC(@d_sql)
PRINT @d_sql
SELECT * FROM  ##tblComments

---

Thanks & Regards, sathya

Answer 2

am not quite sure what you are trying to do with @d_sql afterwards..

declare @d_sql varchar(max)
declare @comments varchar(max)
set @d_sql = '' -- set initial value since @d_sql is currently null
set @comments = 'i can''t go home'
--have you overlooked to add a ' before insert?
set @d_sql = @d_sql + 'insert into #tblComments select ''' + @comments + ''' '  --line causing error
select @d_sql

---

Philippine SQL Server Users Group : www.phissug.org

Answer 3

It's not clear why you use dynamic SQL in your stored procedure, I have a strong feeling that there is no need to at all. But in any case, you should not double any strings in the client code, but pass the input as is. Next in the stored procedure code would go:

SELECT @sql = N'INSERT #tblCommands SELECT @comments'
EXEC sp_executesql @sql, N'@comments varchar(MAX)', @comment

If you have never seen sp_executesql, you need to learn to master it, if you intend to use dynamic SQL. I have an article on my web site that discusses dynamic SQL in detail:

http://www.sommarskog.se/dynamic_sql.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]