'='sp_executesql附近的语法不正确 [英] Incorrect syntax near '=' sp_executesql

问题描述

我需要delete在所有table where值中的所有rows是空字符串.(我有多个具有相似名称的表).

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

我试图执行那些在字符串中的sql语句:

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

但这会引发我错误Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='.

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.

修改: 这是删除最后一个配额后得到的信息. @tableName是nvarchar(MAX).

Here's what I get after deleting last quota. @tableName is nvarchar(MAX).

推荐答案

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;

小提琴

这篇关于'='sp_executesql附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！