无法从动态sql中删除引号

I want to remove quotes from this dynamic sql so it would be just a script.

我想从这个动态sql中删除引号,所以它只是一个脚本。

set @sqlDNB= '
 SELECT DISTINCT
                   Null CustomerID
                   ,EntityID MCVID
                   ,business_name CustomerName
                   ,physical_City +  ',' + physical_state +  '' + cast(physical_zip as varchar(7)) Address
                   ,'' RM
                   ,'' SubGroup
                   ,'' HierarchyType
                   ,'' status 
                   ,DNB.dunsnum DunsNumber
                   ,'DNB' Source
              from SOR.T_DunAndBradstreet DNB with (nolock)
              left outer join SOR.T_McvDuns MCVDUNS with (nolock)
              on DNB.dunsnum  = MCVDUNS.DunsNumber
                +
 case
 when @p_SearchMethod ='BeginsWith' and @p_CustomerName !='' then 'and business_name like ''' + @p_CustomerName + ''' '
 when @p_SearchMethod ='Contains' and @p_CustomerName !=''  then 'and Contains (business_name,''"' + @p_CustomerName + '"'')'
 when @p_SearchMethod ='Exact' and @p_CustomerName !=''  then  'and business_name =''' + @p_CustomerName + ''' ' 
 else
 ''
end 

+ ' where not exists 
(select dunsnumber from sor.T_Customer customer where customer.DUNSnumber = DNB.dunsnum '
+  
case
 when @p_SearchMethod ='BeginsWith' and @p_CustomerName !='' then 'and customer.Name like ''' + @p_CustomerName + ''' '
 when @p_SearchMethod ='Contains' and @p_CustomerName !=''  then 'and Contains (customer.Name,''"' + @p_CustomerName + '"'')'
 when @p_SearchMethod ='Exact' and @p_CustomerName !=''  then  'and customer.Name =''' + @p_CustomerName + ''' ' 
 else
 ''
end 

if @p_TIN_SSN !='' 
begin
set  @sqlDNB =  @sqlDNB + ' and customer.TINorSSN =''' + @p_TIN_SSN + ''' ' 
end
if  @p_CustomerID !='' 
begin
set  @sqlDNB =  @sqlDNB + ' and customer.[MCVID] =''' + cast(@p_CustomerID as varchar(20)) + '''  '  
end 
set  @sqlDNB =  @sqlDNB + ')'

2 个解决方案

#1

Removing any single quotes from the following code will cause an error:

从以下代码中删除任何单引号都会导致错误:

'and business_name like ''' + @p_CustomerName + ''' '

When ever you see '' inside of a string in SQL it is representing a single '. For example

当你在SQL中看到''里面的字符串时它代表一个'。例如

declare @test varchar(12)
set @test = 'don''t'
select @test

Will return don't.

回报不会。

Therefore, removing any single quotes from the code block you mentioned will make your like break.

因此,从您提到的代码块中删除任何单引号将使您喜欢休息。

Hope this makes sense.

希望这是有道理的。

#2

A handy way to 'debug' this kind of scripts in the SQL Server Management Studio is as follows:

在SQL Server Management Studio中“调试”此类脚本的一种方便方法如下:

DECLARE @sqlDNB nvarchar(max);

set @sqlDNB = <your script here>;

PRINT @sqlDNB;

If you run this, you'll get specific error messages that you can use to modify your script. And in the end, the printed string is the SQL script, and you can copy and test that as well.

如果运行此命令,您将获得可用于修改脚本的特定错误消息。最后,打印的字符串是SQL脚本,您也可以复制和测试它。

#1