Help with Dynamic SQL in Store Procedure

• Hogie503

  Mr or Mrs. 500

  Points: 543

  More actions

  March 9, 2018 at 12:01 am

  #344339

  Hello SQL Experts,

  I am trying to create dynamic SQL in a store Procedure for a search form with many search parameters
  where I am trying to avoid a dozen or more (IF ELSE IF Statements)

  The  (Acct_Table.Cancel = 1)  will appear in all the WHERE filter for all the IF statement, so I put that 
  after  WHERE 1 = 1,...  The part that I do not understand how to implement is this condition

  IF @flag = 0    THEN      ( EXP_Table.pay = 0)                                                                 (shown on the last line of the IF @flag =0 AND @type = 1, code segment)
     ELSE IF @flag =     THEN      (EXP_Table.pay = 0  OR  EXP_Table.pay = 1)         ( shown in the last line of the IF @flag = 1 AND @type = 2, code segment)

  IF @flag = 0 appears in any of the IF statement then this filter must be applied (EXP_Table.pay = 0)
  IF @flag = 1 appears in any of the IF statement then this filter must be applied (EXP_Table.pay = 0  OR EXP_Table.pay = 1)

  ALSO.... in terms of performance, will a query with a dozen IF ELSE be better than Dynamic SQL, I am not sure if one will perform better

  Any help will be appreciate

  Thanks

  Below is a sample of what I am trying to accomplish..
  @Year  AS SMALLINT = NULL,
   @Month  AS SMALLINT = NULL,
   @type AS VARCHAR(5) = NULL,
   @debug bit = 0,
   @Phone_Mask AS NVARCHAR(50) = NULL,
   @flag AS bit

   AS
  BEGIN
  DECLARE @SQL NVARCHAR(MAX)
  DECLARE @paramlist NVARCHAR(4000)  
  SELECT @SQL = 
      '  SELECT EXP_Table.Phone_No, 
         Acct_Table.code,
       Acct_Table.cancel,
       EXP_Table.pay,
       Acct_Table.Year,
       Acct_Table.Month,

  FROM      EXP_Table
       INNER JOIN
                   Acct_Table ON EXP_Table.EXP_TableAccountsPK = Acct_Table.EXP_TableAccountsFK
          WHERE  1 = 1' + 'AND Acct_Table.cancel = 1

           
   IF @Year IS NOT NULL
        SELECT @SQL += '  AND  Acct_Table.Year = @Year'

   IF @Month IS NOT NULL
        SELECT @SQL += '  AND  Acct_Table.AjCycle = @month' 

    IF @Phone_Mask IS NOT NULL
         SELECT @SQL += '  AND  (EXP_Table.Phone_No LIKE  @Phone_Mask + ''%'')'

   IF  @type = 1
        SELECT @SQL += '  AND  Acct_Table.code IN (4110,4015)'

          IF  @type = 2
        SELECT @SQL += '  AND  Acct_Table.code IN (4120,4125) '

   IF  @type = 3
        SELECT @SQL += '  AND  Acct_Table.code IN (4130,4135,4210,4215,4230,4235,4240,4245) '

    IF @debug = 1
        PRINT @SQL

         SELECT @paramlist = 
         @Year  SMALLINT,
         @Month SMALLINT ,
         @debug bit,
         @Phone_Mask VARCHAR,
         @flag  VARCHAR,
         @type  VARCHAR'
         
    EXEC sp_executesql @SQL, @paramlist,
                                                     @Year, 
                                                      @month,   
                                                      @type,
                                                     @Phone_Mask,
                                                       @debug,
                                                       @flag 
                  END

   -------     Below is part of the IF ELSE I am trying to do in Dynamic SQL  above  -------------------------------------------------------------------------------------

  IF @flag =0 AND @type = 1
      SELECT......

       WHERE Acct_Table.Month = @Month
   AND  Acct_Table.Year = @Year 
   AND Acct_Table.Code IN (4110,4015)
   AND (EXP_Table.Phone_No LIKE @Phone_Mask)
          AND Acct_Table.Cancel = 1
          AND EXP_Table.pay = 0

  Else IF @flag = 1  AND @type = 2
      SELECT .......

       WHERE Acct_Table.Month = @Month
   AND  Acct_Table.Year = @Year 
   AND Acct_Table.Code IN (4120,4125)
   AND (EXP_Table.Phone_No LIKE @Phone_Mask)
          AND Acct_Table.Cancel = 1
          AND (EXP_Table.pay = 0 OR EXP_Table.pay = 1)

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  March 9, 2018 at 12:43 am

  #1982578

  Whole point of creating a dynamic query is to avoid this kind of scenario. So instead of creating a dynamic query with IF ELSE you should be implementing this flag base logic inside your SP and generate a single dynamic query.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 9, 2018 at 11:07 am

  #1982635

  Try this on for size:CREATE PROCEDURE dbo.YourStoredProc (
    @Year AS smallint = NULL,
    @Month AS smallint = NULL,
    @type AS varchar(5) = NULL,
    @debug AS bit = 0,
    @Phone_Mask AS nvarchar(50) = NULL,
    @flag AS bit
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL AS nvarchar(max) = N'
SELECT EXP_Table.Phone_No,
    Acct_Table.code,
    Acct_Table.cancel,
    EXP_Table.pay,
    Acct_Table.Year,
    Acct_Table.Month,
FROM EXP_Table
    INNER JOIN Acct_Table
        ON EXP_Table.EXP_TableAccountsPK = Acct_Table.EXP_TableAccountsFK
WHERE 1 = 1
    AND Acct_Table.cancel = 1' +
    CASE
        WHEN @Year IS NOT NULL THEN N'
    AND Acct_Table.Year = ' + CONVERT(char(4), @Year)
        ELSE N''
    END +
    CASE
        WHEN @Month IS NOT NULL THEN N'
    AND Acct_Table.AjCycle = ' + CONVERT(varchar(2), @month)
        ELSE N''
    END +
    CASE
        WHEN @Phone_Mask IS NOT NULL THEN N'
    AND (EXP_Table.Phone_No LIKE ''' + @Phone_Mask + N'%'')'
        ELSE N''
    END +
    CASE @type
        WHEN '1' THEN N'
    AND Acct_Table.code IN (4110, 4015)'
        WHEN '2' THEN N'
    AND Acct_Table.code IN (4120, 4125)'
        WHEN '3' THEN N'
    AND Acct_Table.code IN (4130, 4135, 4210, 4215, 4230, 4235, 4240, 4245)'
        ELSE N''
    END +
    CASE @flag
        WHEN 0 THEN N'
    AND EXP_Table.pay = 0;'
        WHEN 1 THEN N'
    AND EXP_Table.pay IN (0, 1);'
        ELSE N';'
    END;

  IF @debug = 1
    BEGIN
    PRINT @SQL;
    END;

  EXEC sp_executesql @SQL;
END;
GO

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1003859

  More actions

  March 9, 2018 at 1:43 pm

  #1982672

  twin.devil - Friday, March 9, 2018 12:43 AM

  Whole point of creating a dynamic query is to avoid this kind of scenario. So instead of creating a dynamic query with IF ELSE you should be implementing this flag base logic inside your SP and generate a single dynamic query.

  Not quite true.  Please see the following article.
  https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Hogie503

  Mr or Mrs. 500

  Points: 543

  More actions

  March 10, 2018 at 10:41 pm

  #1982713

  Steve,
  Thank you very much for your reply, it work great... Really appreciate it...
  ,

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  March 12, 2018 at 4:24 am

  #1982745

  Jeff Moden - Friday, March 9, 2018 1:43 PM

  twin.devil - Friday, March 9, 2018 12:43 AM

  Whole point of creating a dynamic query is to avoid this kind of scenario. So instead of creating a dynamic query with IF ELSE you should be implementing this flag base logic inside your SP and generate a single dynamic query.

  Not quite true.  Please see the following article.
  https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  Jeff, I know this method and this article and i haven't said anything against it either. My comments was solely against the below output of the dynamic query shared by Op's.
  

  -------  Below is part of the IF ELSE I am trying to do in Dynamic SQL above -------------------------------------------------------------------------------------IF @flag =0 AND @type = 1
    SELECT......  WHERE Acct_Table.Month = @Month
  AND Acct_Table.Year = @Year
  AND Acct_Table.Code IN (4110,4015)
  AND (EXP_Table.Phone_No LIKE @Phone_Mask)
     AND Acct_Table.Cancel = 1
     AND EXP_Table.pay = 0Else IF @flag = 1 AND @type = 2
    SELECT .......  WHERE Acct_Table.Month = @Month
  AND Acct_Table.Year = @Year
  AND Acct_Table.Code IN (4120,4125)
  AND (EXP_Table.Phone_No LIKE @Phone_Mask)
     AND Acct_Table.Cancel = 1
     AND (EXP_Table.pay = 0 OR EXP_Table.pay = 1)

  So instead of applying IF ELSE inside the dynamic query, SP should contain this logic.

• Jeff Moden

  SSC Guru

  Points: 1003859

  More actions

  March 12, 2018 at 7:53 am

  #1982759

  twin.devil - Monday, March 12, 2018 4:24 AM

  Jeff Moden - Friday, March 9, 2018 1:43 PM

  twin.devil - Friday, March 9, 2018 12:43 AM

  Whole point of creating a dynamic query is to avoid this kind of scenario. So instead of creating a dynamic query with IF ELSE you should be implementing this flag base logic inside your SP and generate a single dynamic query.

  Not quite true.  Please see the following article.
  https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  Jeff, I know this method and this article and i haven't said anything against it either. My comments was solely against the below output of the dynamic query shared by Op's.
  

  -------  Below is part of the IF ELSE I am trying to do in Dynamic SQL above -------------------------------------------------------------------------------------IF @flag =0 AND @type = 1
    SELECT......  WHERE Acct_Table.Month = @Month
  AND Acct_Table.Year = @Year
  AND Acct_Table.Code IN (4110,4015)
  AND (EXP_Table.Phone_No LIKE @Phone_Mask)
     AND Acct_Table.Cancel = 1
     AND EXP_Table.pay = 0Else IF @flag = 1 AND @type = 2
    SELECT .......  WHERE Acct_Table.Month = @Month
  AND Acct_Table.Year = @Year
  AND Acct_Table.Code IN (4120,4125)
  AND (EXP_Table.Phone_No LIKE @Phone_Mask)
     AND Acct_Table.Cancel = 1
     AND (EXP_Table.pay = 0 OR EXP_Table.pay = 1)

  So instead of applying IF ELSE inside the dynamic query, SP should contain this logic.

  Ah... got it.  Thanks for the feedback.  And, yes... I agree.  No IF/ELSE in the dynamic SQL itself.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  March 12, 2018 at 8:23 am

  #1982769

  Jeff Moden - Monday, March 12, 2018 7:53 AM

  twin.devil - Monday, March 12, 2018 4:24 AM

  Jeff Moden - Friday, March 9, 2018 1:43 PM

  twin.devil - Friday, March 9, 2018 12:43 AM

  Whole point of creating a dynamic query is to avoid this kind of scenario. So instead of creating a dynamic query with IF ELSE you should be implementing this flag base logic inside your SP and generate a single dynamic query.

  Not quite true.  Please see the following article.
  https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  Jeff, I know this method and this article and i haven't said anything against it either. My comments was solely against the below output of the dynamic query shared by Op's.
  

  -------  Below is part of the IF ELSE I am trying to do in Dynamic SQL above -------------------------------------------------------------------------------------IF @flag =0 AND @type = 1
    SELECT......  WHERE Acct_Table.Month = @Month
  AND Acct_Table.Year = @Year
  AND Acct_Table.Code IN (4110,4015)
  AND (EXP_Table.Phone_No LIKE @Phone_Mask)
     AND Acct_Table.Cancel = 1
     AND EXP_Table.pay = 0Else IF @flag = 1 AND @type = 2
    SELECT .......  WHERE Acct_Table.Month = @Month
  AND Acct_Table.Year = @Year
  AND Acct_Table.Code IN (4120,4125)
  AND (EXP_Table.Phone_No LIKE @Phone_Mask)
     AND Acct_Table.Cancel = 1
     AND (EXP_Table.pay = 0 OR EXP_Table.pay = 1)

  So instead of applying IF ELSE inside the dynamic query, SP should contain this logic.

  Ah... got it.  Thanks for the feedback.  And, yes... I agree.  No IF/ELSE in the dynamic SQL itself.

  Thanks for your feedback 🙂

• Hogie503

  Mr or Mrs. 500

  Points: 543

  More actions

  March 12, 2018 at 11:20 am

  #1982801

  Thank you all for your reply's, and comments, as a rookie I am learning a lot from the exchanges between you experts, especially the do's and don't when creating Dynamic SQL..
  I Really appreciate all the feedbacks.

• oughtsix

  SSC-Addicted

  Points: 448

  More actions

  March 12, 2018 at 11:55 am

  #1982813

  I would not do this with dynamic sql.  I would just use a straight up sql statement.

      
SELECT EXP_Table.Phone_No,
  Acct_Table.code,
  Acct_Table.cancel,
  EXP_Table.pay,
  Acct_Table.Year,
  Acct_Table.Month,
FROM EXP_Table
  INNER JOIN Acct_Table
   ON EXP_Table.EXP_TableAccountsPK = Acct_Table.EXP_TableAccountsFK
WHERE 
(@Year is null or Acct_Table.Year = @Year)
AND (@Month is null or Acct_Table.AjCycle = @Month)
AND (@Phone_Mask is null or EXP_Table.Phone_No LIKE @Phone_Mask + N'%')
AND (
    @Type is null
    OR (@Type = 1 and Acct_Table.code IN (4110, 4015))
    OR (@Type = 2 and Acct_Table.code IN (4120, 4125))
    OR (@Type = 3 and Acct_Table.code IN (4130, 4135, 4210, 4215, 4230, 4235, 4240, 4245))
)
AND (
    @Flag is null
    OR (@flag = 0 and EXP_Table.pay = 0)
    OR (@flag = 1 and EXP_Table.pay IN (0, 1))
)


  I don't understand why you would want to go through all the complexity of making a dynamic SQL statement for this?

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  March 12, 2018 at 1:50 pm

  #1982823

  oughtsix - Monday, March 12, 2018 11:55 AM

  I would not do this with dynamic sql.  I would just use a straight up sql statement.
  I don't understand why you would want to go through all the complexity of making a dynamic SQL statement for this?
  

  Then you should probably read the article linked in Jeff Moden's earlier post about catch-all-queries.  While this type of statement may be easier for the developer to write, there is no good way for the database engine to produce an optimized execution plan that would work for all those different scenarios.

• oughtsix

  SSC-Addicted

  Points: 448

  More actions

  March 12, 2018 at 9:07 pm

  #1982850

  Isn't that what query hints are for?

  "Optimize for" usually helps a lot in these situations.  Dynamic sql would still be my last choice to handle performance problems.

  https://msdn.microsoft.com/en-us/library/ms181714(v=sql.110).aspx

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 13, 2018 at 9:17 am

  #1982956

  oughtsix - Monday, March 12, 2018 11:55 AM

  I would not do this with dynamic sql.  I would just use a straight up sql statement.

      
SELECT EXP_Table.Phone_No,
  Acct_Table.code,
  Acct_Table.cancel,
  EXP_Table.pay,
  Acct_Table.Year,
  Acct_Table.Month,
FROM EXP_Table
  INNER JOIN Acct_Table
   ON EXP_Table.EXP_TableAccountsPK = Acct_Table.EXP_TableAccountsFK
WHERE 
(@Year is null or Acct_Table.Year = @Year)
AND (@Month is null or Acct_Table.AjCycle = @Month)
AND (@Phone_Mask is null or EXP_Table.Phone_No LIKE @Phone_Mask + N'%')
AND (
    @Type is null
    OR (@Type = 1 and Acct_Table.code IN (4110, 4015))
    OR (@Type = 2 and Acct_Table.code IN (4120, 4125))
    OR (@Type = 3 and Acct_Table.code IN (4130, 4135, 4210, 4215, 4230, 4235, 4240, 4245))
)
AND (
    @Flag is null
    OR (@flag = 0 and EXP_Table.pay = 0)
    OR (@flag = 1 and EXP_Table.pay IN (0, 1))
)


  I don't understand why you would want to go through all the complexity of making a dynamic SQL statement for this?

  Because performance can suffer horribly with catch-all queries...

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  March 13, 2018 at 9:29 am

  #1982961

  ...and right on cue, Gail writes an updated article about catch-all-queries and her latest thoughts on it:
  https://www.sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

• oughtsix

  SSC-Addicted

  Points: 448

  More actions

  March 13, 2018 at 2:39 pm

  #1983050

  Chris Harshman - Tuesday, March 13, 2018 9:29 AM

  ...and right on cue, Gail writes an updated article about catch-all-queries and her latest thoughts on it:
  https://www.sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

  I like her approach of using query hints... specifically RECOMPILE much better than resorting to dynamic SQL.  My understanding of her article is that she now also prefers the RECOMPILE query hint over dynamic SQL... if I understood the article correctly?