CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Arpita Dutta
Arpita Dutta

Reputation: 91

Issue with single quotes while executing stored procedure

I have a stored procedure. Please check below the procedure description. If I pass @PayerName = '2342342', the stored procedure is throwing error Incorrect syntax near '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

Upvotes: 0

Views: 762

Answers (2)

Thom A
Thom A

Reputation: 95561

As has been mentioned in the comments, there is no need for dynamic SQL here. There are 2 non-dynamic options you can go for here; for what you have there's unlikely to be much of a performance difference if any. Personally I tend to use an OR, however, (as Panagiotis Kanavos mentions in the comments) you can also use IF logic.

OR Logic (with 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 logic

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

If you "must" use dynamic SQL, then you need to properly parametrise the query, not inject the parameter:

Parametrised Dynamic Query

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

Upvotes: 2

Popeye
Popeye

Reputation: 35900

Don't use the dynamic query at all. Not able to figure out the issue but you can simply use the following query:

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)

Upvotes: 0

Related Questions