Incorrect syntax near '=' sp_executesql

Question

I need to delete all rows in some table where value is empty string.(I have multiple table which got similar name).

I tryed to execute those sql statement which is in string:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @emptyValue AS NVARCHAR(1) =''    
set @sql = N'DELETE FROM SampleTable WHERE Value='+@emptyValue+''
exec sp_executesql @sql

But it's throw me error Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='.

I tryed to figure it out about an hour now. Any help would be appreciated.

Edit: Here's what I get after deleting last quota. @tableName is nvarchar(MAX). enter image description here

StuartLC · Accepted Answer · 2014-02-27T11:15:35.240

5

Instead of doing string concatenation, parameterize the call to sp_executesql, for Sql Injection and other reasons (including caching of query plans, and not having to worry about escaping quotes :-):

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @emptyValue AS NVARCHAR(1) ='';
set @sql = N'DELETE FROM SampleTable WHERE Value=@emptyValue';
exec sp_executesql @sql, N'@emptyValue NVARCHAR(1)', @emptyValue = @emptyValue;

Fiddle

edited Feb 27 '14 at 11:15

answered Feb 27 '14 at 11:03

StuartLC

104,537
17
209
285

If he must use exec then this is the best way to do it. – Fedor Hajdu Feb 27 '14 at 11:05

Darren · Answer 2 · 2014-02-27T11:07:39.167

1

You have two quotes:

set @sql = N'DELETE FROM SampleTable WHERE Value='+@emptyValue+''

Change it to:

set @sql = N'DELETE FROM SampleTable WHERE Value='+@emptyValue

http://sqlfiddle.com/#!3/ce8e3/4

edited Feb 27 '14 at 11:07

answered Feb 27 '14 at 10:58

Darren

68,902
24
138
144

with single quota at the end the rest of my script is treated as string. – Harry89pl Feb 27 '14 at 11:01
@harry180 - you have two single quotes. You only need one. I have included a SQL Fiddle to demonstrate my code working. – Darren Feb 27 '14 at 11:02
@DarrenDavies It appears what is in your SQLFiddle is not the same as the code you've posted here. From your fiddle: `set @sql = N'DELETE FROM SampleTable WHERE Value='+@emptyValue+''` - Note the two quotes at the end, not one. – Bridge Feb 27 '14 at 11:06
After deleting quotas from the end it's still returns same error :( – Harry89pl Feb 27 '14 at 11:10
I think the problem is that from the default value assigned to `@emptyValue` in the question, it's inferred that the Value column is a string - you need to surround it in single quotes. – Bridge Feb 27 '14 at 11:10

score 0 · Answer 3 · edited May 23 '17 at 12:20

If you can, I'd go with StuartLC's answer - parameters are clearly the way to go.

Just to show you what to do if you have no choice but to go this way, you can escape the single quotes. The quotes are necessary as the Value column appears to be a string (inferred from the default value set to @emptyValue in the question).

To escape a single quote, you need another single quote. Give this a try:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @emptyValue AS NVARCHAR(1) = ''
set @sql = N'DELETE FROM SampleTable WHERE Value='''+@emptyValue+''''
exec sp_executesql @sql

Note you could always PRINT @sql to see the script, rather than blindly trying to run it.

Incorrect syntax near '=' sp_executesql

3 Answers3

Linked