执行存储过程时出现单引号问题

Question

我有一个存储过程。请检查下面的程序说明。如果我通过@PayerName='2342342'，则存储过程将抛出错误'2342342'附近的语法不正确

CREATE PROCEDURE GetPayer(@PayerName VARCHAR(50) = '')
AS
BEGIN
SET NOCOUNT ON
--
DECLARE @Qry NVARCHAR(MAX)
--
SET @Qry = 'SELECT CM.CODE AS PAYER_CODE,
CM.NAME AS PAYER_NAME,
CM.ADDR_1 AS ADDRESS,                            
FROM CUST_MASTER CM    
WHERE CM.ISDELETED = ''N'' ' 
IF(@PayerName IS NOT NULL AND @PayerName <> '' )
SET @Qry = @Qry + 'AND (CM.NAME LIKE ''%' + @PayerName + '%'' OR CM.CODE LIKE ''%' + @PayerName + '%'')'
--  
EXEC SP_EXECUTESQL  @Qry
--  
END

Answer 1

正如评论中所提到的，这里不需要动态SQL。这里有2个非动态选项可供选择；就你所拥有的而言，不太可能有太大的性能差异(如果有的话(。然而，就我个人而言，我倾向于使用OR(正如Panagiotis Kanavos在评论中提到的那样(，您也可以使用IF逻辑。

OR逻辑(带OPTION (RECOMPILE)(

CREATE PROC dbo.GetPayer @PayerName varchar(50) = NULL AS --You're better off using NULL here in my opinion
BEGIN
SET NOCOUNT ON;
SELECT CM.CODE AS PAYER_CODE,
CM.NAME AS PAYER_NAME,
CM.ADDR_1 AS ADDRESS,                            
FROM CUST_MASTER CM    
WHERE CM.ISDELETED = 'N'
AND ((CM.NAME LIKE '%' + @PayerName + '%' OR CM.CODE LIKE '%' + @PayerName + '%')
OR  @PayerName IS NULL)
OPTION (RECOMPILE);
END;
GO

IF逻辑

CREATE PROC dbo.GetPayer @PayerName varchar(50) = NULL AS --You're better off using NULL here in my opinion
BEGIN
SET NOCOUNT ON;
IF @PayerName IS NULL
SELECT CM.CODE AS PAYER_CODE,
CM.NAME AS PAYER_NAME,
CM.ADDR_1 AS ADDRESS,                            
FROM CUST_MASTER CM    
WHERE CM.ISDELETED = 'N';
ELSE
SELECT CM.CODE AS PAYER_CODE,
CM.NAME AS PAYER_NAME,
CM.ADDR_1 AS ADDRESS,                            
FROM CUST_MASTER CM    
WHERE CM.ISDELETED = 'N'
AND (CM.NAME LIKE '%' + @PayerName + '%' OR CM.CODE LIKE '%' + @PayerName + '%');
END;
GO

如果你"；必须"；使用动态SQL，则需要正确地参数化查询，而不是注入参数：

参数化动态查询

CREATE PROC dbo.GetPayer @PayerName varchar(50) = NULL AS --You're better off using NULL here in my opinion
BEGIN
SET NOCOUNT ON;
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT CM.CODE AS PAYER_CODE,' + @CRLF +
N'     CM.NAME AS PAYER_NAME,' + @CRLF +
N'     CM.ADDR_1 AS ADDRESS,' + @CRLF +                
N'FROM CUST_MASTER CM' + @CRLF +
N'WHERE CM.ISDELETED = ''N''' +
CASE WHEN @PayerName IS NOT NULL THEN @CRLF + N'  AND (CM.NAME LIKE ''%'' + @PayerName + ''%'' OR CM.CODE LIKE ''%'' + @PayerName + ''%'');' ELSE N';' END;
EXEC sys.sp_executesql @SQL, N'@PayerName varchar(50)', @PayerName;
END;
GO

Answer 2

根本不要使用动态查询。无法解决问题，但您可以简单地使用以下查询：

SELECT CM.CODE AS PAYER_CODE,
CM.NAME AS PAYER_NAME,
CM.ADDR_1 AS ADDRESS,                            
FROM CUST_MASTER CM    
WHERE CM.ISDELETED = 'N'
AND (CM.NAME LIKE '%' + @PayerName + '%' 
OR CM.CODE LIKE '%' + @PayerName + '%'
OR  @PayerName IS NULL)