Specify String In Stored procedure query

Question

I have a query that I am running in a stored procedure. However, is produces the error:

Incorrect syntax near 'CHEQUE'

The query is:

SELECT @QUERY1 = 'UPDATE  [dbo].[ATAB] SET PAYMCODE='CHQ' WHERE RATE=1'

How do I specify this string 'CHQ' without getting an error?

Answer 1

When specifying a string literal within a dynamic SQL string you have to escape the single quotation with another single quotation (ex: '', not " which is double quotation) So the query will be like this:

SELECT @QUERY1 = 'UPDATE  [dbo].[ATAB] SET PAYMCODE=''CHQ'' WHERE RATE=1'

This will translate it to:

UPDATE [dbo].[ATAB] SET PAYMCODE='CHQ' WHERE RATE=1

You can also use Nate S's answer if you want to store CHQ into a variable, or use EXEC with specifying parameters like this:

DECLARE @Paymcode varchar(3) = 'CHQ'
DECLARE @SQL nvarchar(max)
DECLARE @Params nvarchar(max)

SET @SQL = N'UPDATE [dbo].[ATAB] SET PAYMCODE=@innerPaymcode WHERE RATE=1'
SET @Params = N'@innerPaymcode varchar(3)'

EXEC sp_executesql @SQL, @innerPaymcode = @Paymcode