Search code examples
sqlsql-servert-sqlsp-executesql

Incorrect syntax near '=' sp_executesql

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

Solution

  • 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