How do I change a SELECT statement in a stored procedure based on the value of a variable?

• Lorna-331036

  SSC Eights!

  Points: 867

  More actions

  January 23, 2019 at 7:01 am

  #376869

  I've got a web page (.Net web forms) where a user can display sales invoices by date only or date and customer id.  I'm trying to avoid creating 2 SPs to do this, but how do I do it in the same SP using an IF ELSE statement?  If the user doesn't select a customer, the CustID will be 0.
  So far I have (which is obviously wrong):

  CREATE PROCEDURE [spSalesInvoices]
  (
  @OrderDate datetime,
  @CustID int
  )
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

     IF (@CustID = 0) THEN
          SELECT *
          FROM vSalesInvoices
          WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
          AND Delivery_Date= @OrderDate
          ORDER BY Company_Name
          END
      ELSE
          SELECT *
          FROM vSalesOrders
          WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
          AND Delivery_Date= @OrderDate
          AND CustomerID = @CustID
          ORDER BY Company_Name
      END IF

• BrainDonor

  SSCoach

  Points: 19254

  More actions

  January 23, 2019 at 7:37 am

  #2019809

  Have you looked at dynamic sql, using sp_executesql?

  Steve Hall
  Linkedin
  Blog Site

• Jeff Moden

  SSC Guru

  Points: 1004313

  More actions

  January 23, 2019 at 7:39 am

  #2019811

  Please see the following article.  I consider it to be the "GO TO" article for such queries.
  https://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)
  

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 23, 2019 at 8:24 am

  #2019823

  Lorna-331036 - Wednesday, January 23, 2019 7:01 AM

  I've got a web page (.Net web forms) where a user can display sales invoices by date only or date and customer id.  I'm trying to avoid creating 2 SPs to do this, but how do I do it in the same SP using an IF ELSE statement?  If the user doesn't select a customer, the CustID will be 0.
  So far I have (which is obviously wrong):

  CREATE PROCEDURE [spSalesInvoices]
  (
  @OrderDate datetime,
  @CustID int
  )
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

     IF (@CustID = 0) THEN
          SELECT *
          FROM vSalesInvoices
          WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
          AND Delivery_Date= @OrderDate
          ORDER BY Company_Name
          END
      ELSE
          SELECT *
          FROM vSalesOrders
          WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
          AND Delivery_Date= @OrderDate
          AND CustomerID = @CustID
          ORDER BY Company_Name
      END IF

  Check the correct syntax for IF on T-SQL. We don't use THEN or END IF.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lorna-331036

  SSC Eights!

  Points: 867

  More actions

  January 23, 2019 at 9:14 am

  #2019833

  Thanks all - I followed the link from Jeff and created the following stored procedure which I think is almost there, but I'm mow getting an error:

  Conversion failed when converting the varchar value 'ORDER BY Company_Name' to data type int.

  Here is my stored procedure:
  [spSalesOrders]
  (
  @OrderDate datetime,
  @CustID int
  )
  AS
  DECLARE @startSQL nvarchar(255), @SQLString nvarchar(255)

  SET @startSQL = 'SELECT * FROM vSalesInvoices WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5) AND Delivery_Date= @OrderDate'

  IF (@CustID = 0)
  SET @SQLString = @SQLString + 'ORDER BY Company_Name'
  ELSE
  SET @SQLString = @SQLString + 'AND CustomerID = ' + @CustID + 'ORDER BY Company_Name'

  EXEC sp_executesql @SQLString

  Can anyone spot where I'm going wrong? Thanks 🙂

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 23, 2019 at 9:30 am

  #2019838

  Lorna-331036 - Wednesday, January 23, 2019 9:14 AM

  Thanks all - I followed the link from Jeff and created the following stored procedure which I think is almost there, but I'm mow getting an error:

  Conversion failed when converting the varchar value 'ORDER BY Company_Name' to data type int.

  Here is my stored procedure:
  [spSalesOrders]
  (
  @OrderDate datetime,
  @CustID int
  )
  AS
  DECLARE @startSQL nvarchar(255), @SQLString nvarchar(255)

  SET @startSQL = 'SELECT * FROM vSalesInvoices WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5) AND Delivery_Date= @OrderDate'

  IF (@CustID = 0)
  SET @SQLString = @SQLString + 'ORDER BY Company_Name'
  ELSE
  SET @SQLString = @SQLString + 'AND CustomerID = ' + @CustID + 'ORDER BY Company_Name'

  EXEC sp_executesql @SQLString

  Can anyone spot where I'm going wrong? Thanks 🙂

  You shouldn't concatenate values in your dynamic sql strings. That's why sp_executesql has the option to use parameters. In your previous post, you were using two different views, in this one you're using the same one. What should it be? I don't believe that dynamic sql is the best option for your problem.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ninjamy

  SSC Enthusiast

  Points: 117

  More actions

  January 23, 2019 at 8:11 pm

  #2019897

  @CustID is set to an INT, needs go be cast to a character to work.  Change to Cast(@CustID as Varchar). Should work then. Definitely look up sp_executesql and the benefits when using dynamic sql though

• Lorna-331036

  SSC Eights!

  Points: 867

  More actions

  January 24, 2019 at 6:31 am

  #2019940

  I tried CAST and CONVERT but it's still throwing up errors.  I need to learn more about spExecute but don't have time just now, so I'm just calling 2 separate sprocs from my c# depending on whether the user selects a customer as well as dates or just dates.  Thanks for all your help.

• ninjamy

  SSC Enthusiast

  Points: 117

  More actions

  January 24, 2019 at 8:13 am

  #2019966

  
  DECLARE @SQLString nvarchar(255)

  SET @SQLString = 'SELECT * FROM vSalesInvoices WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5) AND Delivery_Date= ''' + Convert(Varchar,@OrderDate,1) + ''''

  IF (@CustID = 0)
  SET @SQLString = @SQLString + ' ORDER BY Company_Name'
  ELSE
  SET @SQLString = @SQLString + ' AND CustomerID = ' + Cast(@CustID as Varchar) + ' ORDER BY Company_Name'

  Try this.  Doing a Print @SQLString will help when verifying that you are putting your query together correctly.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 24, 2019 at 8:49 am

  #2019977

  Lorna-331036 - Thursday, January 24, 2019 6:31 AM

  I tried CAST and CONVERT but it's still throwing up errors.  I need to learn more about spExecute but don't have time just now, so I'm just calling 2 separate sprocs from my c# depending on whether the user selects a customer as well as dates or just dates.  Thanks for all your help.

  If you don't have time to learn how to use Dynamic SQL correctly, I hope you have time to fix all the problems that SQL Injection can cause.
  I still don't see any need of using Dynamic SQL.
  
SELECT *
FROM vSalesInvoices
WHERE Order_Status IN( 0, 4, 5)
AND Delivery_Date= @OrderDate
AND ( CustomerID = @CustID OR @CustID = 0)
ORDER BY Company_Name
OPTION (RECOMPILE);

  Read more about catch-all queries in this article: Gail Shaw’s SQL Server Howlers

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• kaj

  SSCommitted

  Points: 1605

  More actions

  January 25, 2019 at 4:02 am

  #2020082

  Lorna-331036 - Wednesday, January 23, 2019 7:01 AM

  I've got a web page (.Net web forms) where a user can display sales invoices by date only or date and customer id.  I'm trying to avoid creating 2 SPs to do this, but how do I do it in the same SP using an IF ELSE statement?  If the user doesn't select a customer, the CustID will be 0.
  So far I have (which is obviously wrong):

  CREATE PROCEDURE [spSalesInvoices]
  (
  @OrderDate datetime,
  @CustID int
  )
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

     IF (@CustID = 0) THEN
          SELECT *
          FROM vSalesInvoices
          WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
          AND Delivery_Date= @OrderDate
          ORDER BY Company_Name
          END
      ELSE
          SELECT *
          FROM vSalesOrders
          WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
          AND Delivery_Date= @OrderDate
          AND CustomerID = @CustID
          ORDER BY Company_Name
      END IF

  If you want to stick to "static" SQL, you could probably just make a few adjustments to your original sp:

  CREATE PROCEDURE [spSalesInvoices]
(
    @OrderDate datetime,
    @CustID int = 0
)
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

   IF (@CustID = 0)
   SELECT *
   FROM vSalesInvoices
   WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
   AND Delivery_Date= @OrderDate
   ORDER BY Company_Name

    ELSE
   SELECT *
   FROM vSalesInvoices
   WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
   AND Delivery_Date= @OrderDate
   AND CustomerID = @CustID
   ORDER BY Company_Name

  END

  As was pointed out by others, you made a few errors in the application of the IF/ELSE statements.

  I have taken the liberty of assigning zero as the default value on the @CustID parameter, so that if this parameter isn't sent with the call to the sp, zero is assumed.

  I have also changed the FROM table/view to be vSalesInvoices in both cases as this seemed the more logical choice, but I may be wrong there of course. 🙂

• Jeff Moden

  SSC Guru

  Points: 1004313

  More actions

  January 25, 2019 at 6:03 am

  #2020091

  Luis and I have both pointed to Gail Shaw's "Catch All Query" article.  If you don't have time to do such a thing then you'll need to make time to fix performance problems and maybe explain how you got hacked by SQL Injection attacks if you use Dynamic SQL improperly.

  Just do it. 😉

  --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)