How do I set paramenters in SQL with if and else ?

Question

0.00/5 (No votes)

See more:

Hello Everyone,

I have this SQL Code that I use for my data charts in Visual Studio (vb.net) and i need to set values to this parameter with the condition but I don't know how:

ALTER Procedure Chart_daily1
(
 @DateD VARCHAR(50)
   ,@Department VARCHAR(50)
)
AS

 Begin

    SELECT    
          CONVERT(char(7), date, 120)  AS 'Month',
          DATEPART(DAY, Date) AS 'Day',
          SUM(Price) AS 'Total'
		  ,Department
FROM      [dbo].[Expenses]
where CONVERT(char(7), date, 120) = @DateD and Department =@Department
GROUP BY  DATEPART(DAY, Date),
         CONVERT(char(7), date, 120) ,
         -- DATEPART(YEAR, Date),
		  department
ORDER BY  
         Department,
          'Month',
          'Day'
   End

This is the one that I use for separate Department values to my data chart, I have a Combobox in my vb.net form which is set to a specific department(@Department) and it shows me data, and what I want now is to get all the departments when I set the value in my Combobox to 'ALL'

What I have tried:

I googled if I can set a value to the parameter in SQL and I thought if I could do it like this :

IF @Department ='All'
   set @Department IN ( 'Grocery','Electronics','Clothes','Vacation','Other','Fix Cost')

...and then the rest of the SQL code.

Any help is appreciated.
Thanks

Posted 22-Jul-20 4:22am

Member 13410460

Updated 22-Jul-20 5:42am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

raddevus · Accepted Answer · 2020-07-22T04:49:00

SQL

ALTER Procedure Chart_daily1
(
 @DateD VARCHAR(50)
   ,@Department VARCHAR(50)
)
AS

Begin
    -- HERE IS NEW CODE
    IF @Department = 'ALL' -- check if @Deparment is sending in the ALL value
    BEGIN
    SELECT    
          CONVERT(char(7), date, 120)  AS 'Month',
          DATEPART(DAY, Date) AS 'Day',
          SUM(Price) AS 'Total' ,Department
          FROM      [dbo].[Expenses]
          where CONVERT(char(7), date, 120) = @DateD --removed department returns all
          GROUP BY  DATEPART(DAY, Date),
          CONVERT(char(7), date, 120) ,
         -- DATEPART(YEAR, Date),
		  department
          ORDER BY  
          Department,
          'Month',
          'Day'
    END
    ELSE -- HERE IS OLD CODE (DO ORIGINAL QUERY WITH @DEPARTMENT) 
          SELECT    
          CONVERT(char(7), date, 120)  AS 'Month',
          DATEPART(DAY, Date) AS 'Day',
          SUM(Price) AS 'Total'
		  ,Department
          FROM      [dbo].[Expenses]
          where CONVERT(char(7), date, 120) = @DateD and Department =@Department
          GROUP BY  DATEPART(DAY, Date),
         CONVERT(char(7), date, 120) ,
         -- DATEPART(YEAR, Date),
		  department
         ORDER BY  
         Department,
          'Month',
          'Day'
End

CHill60 · Accepted Answer · 2020-07-22T05:42:00

An alternative to Solution 1 is to use CASE in the WHERE clause then you don't need the IF e.g.

SQL

SELECT
    CONVERT(char(7), [date], 120)  AS 'Month',
    DATEPART(DAY, [Date]) AS 'Day',
    SUM(Price) AS 'Total' ,Department
FROM Expenses
WHERE CONVERT(char(7), date, 120) = @DateD 
AND Department LIKE CASE WHEN @department = 'ALL' THEN '%' ELSE @department END
GROUP BY  DATEPART(DAY, [Date]), CONVERT(char(7), [Date], 120) ,department
ORDER BY  Department, 'Month','Day'