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?
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
You have to escape your single quotes.
SELECT @QUERY1 = 'UPDATE [dbo].[ATAB] SET PAYMCODE=''CHQ'' WHERE RATE=1'
Just to be different...
Declare @PaymMode varchar(3) = 'CHQ';
SELECT @QUERY1 = 'UPDATE [dbo].[ATAB] SET PAYMCODE='+@PaymMode+' WHERE RATE=1';
You need to escape the single quotes in your code
SELECT @QUERY1 = 'UPDATE [dbo].[ATAB] SET PAYMCODE = ''CHQ'' WHERE RATE = 1'