CODEWITHSUNDEEP
sql
newbie
newbie

Reputation: 14950

Exec SQL error because of single quotes inside

I have the following exec query:

EXEC (
        'SELECT (SELECT Count(*) AS Count_Id FROM ns_customer WHERE external_id IN (
        ' RW00363075 ',' RW00336566 ',' RW00365438 ',' RW00343570 ')  ) AS Count_Id, 
        ACH_allowed, CC_allowed, DD_allowed FROM ns_customer 
        WHERE external_id IN (' RW00363075 ',' RW00336566 ',' RW00365438 ',
        ' RW00343570 ')'
        );

After execution I get an error:

SQL statement: Incorrect syntax near 'RW00363075'.; Caused by: Incorrect syntax near 'RW00363075'.

How can I solve this?

Thanks

Upvotes: 2

Views: 1296

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The answer to your specific question is to double up the single quotes. However, (assuming that you are using SQL Server), your query would be more simply written as:

SELECT COUNT(*) OVER () as Count_Id, ACH_allowed, CC_allowed, DD_allowed
FROM ns_customer 
WHERE external_id IN ('RW00363075', 'RW00336566', 'RW00365438', 'RW00343570')

Or, as I prefer, use sp_executesql:

declare @sql nvarchar(max);
set @sql = '
SELECT COUNT(*) OVER () as Count_Id, ACH_allowed, CC_allowed, DD_allowed
FROM ns_customer 
WHERE external_id IN (''RW00363075'', ''RW00336566'', ''RW00365438'', ''RW00343570'')';

exec sp_executesql @sql;

If you are going to learn how to do this, you might as well learn the right way. sp_executesql is superior to exec() because it allows you to pass parameters in and out of the query. Learning to use it first is simpler than learning it later.

Upvotes: 3

meda
meda

Reputation: 45500

Escape single quotes with two single quotes 

EXEC('SELECT (SELECT Count(*) AS Count_Id FROM ns_customer WHERE external_id IN (''RW00363075'',''RW00336566'',''RW00365438'',''RW00343570'')  ) AS Count_Id, ACH_allowed, CC_allowed, DD_allowed FROM ns_customer 
    WHERE external_id IN (''RW00363075'',''RW00336566'',''RW00365438'',''RW00343570'')');

Upvotes: 8

Related Questions