how to use switch statement with case in SQL

Question

1.00/5 (1 vote)

See more:

HEllo Team,

I have the Code AS Follows:
I want to use Switch statement in Following SP.

ALTER PROCEDURE [dbo].[DEORecordCount]
 @Case int=0
AS BEGIN
 
	if(@Case=1)
		BEGIN
			select U.NETWORKID, count(c.ID) as DEORECORDCOUNTDONE
			from CUSTOMERLOANDATA C (nolock)
			Inner Join USERMASTER U (nolock) on U.Id = C.CREATEDBY
			where convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10),'2014-02-28 12:48:57.000',120)
			--where convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10), GETDATE(),120)
			group by U.NETWORKID
			order by U.NETWORKID
	 END
 ELSE IF(@Case=2)
	 BEGIN
		------------------FOR PENDING------------------
		select U.NETWORKID, count(c.ID) as DEORecordCountPending
		from CUSTOMERLOANOCR C (nolock)
		Inner Join USERMASTER U (nolock) on U.Id = C.ASSIGNEDTO
		where
		c.createdon <= '2014-06-01 ' 
--where convert(varchar(10),c.createdon,120)<= CONVERT(VARCHAR(10), GETDATE(),120)
		group by U.NETWORKID
		order by U.NETWORKID
	 END

end

Posted 17-Apr-14 6:04am

R Harshal

Add a Solution

Comments

Richard C Bishop 17-Apr-14 12:20pm

SQL does not have a switch case. You can use Case Statements.

Sanket Saxena 17-Apr-14 12:24pm

exactly

Nirav Prabtani 18-Apr-14 1:01am

have you passed @Case variable???

R Harshal 18-Apr-14 5:10am

Thank you so much For the Reply.Its Done.

4 solutions

Solution 1

You should use like example below:

SQL

declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
@locationID = 
  CASE @locationType
      WHEN 'location' THEN account_location
      WHEN 'area' THEN xxx_location_area 
      WHEN 'division' THEN xxx_location_division 
  END

No need to use if(@Case=1) etc

Posted 17-Apr-14 6:20am

Sanket Saxena

Comments

R Harshal 18-Apr-14 5:09am

Thank you so much For the Reply.

Solution 3

Refer: How to use switch - case statement in T-SQL..?[^]

Posted 17-Apr-14 18:57pm

Peter Leow

Comments

R Harshal 18-Apr-14 5:10am

Thank you so much For the Reply.

Solution 4

Google it..

CASE (Transact-SQL)

Posted 17-Apr-14 19:07pm

King Fisher

Comments

R Harshal 18-Apr-14 5:10am

Thank you so much For the Reply.

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)

DamithSL · Accepted Answer · 2014-04-17T06:24:00

you can use where condition as below, no need case statement

SQL

select U.NETWORKID, count(c.ID) as DEORECORDCOUNTDONE
from CUSTOMERLOANDATA C (nolock)
Inner Join USERMASTER U (nolock) on U.Id = C.CREATEDBY
where 
   (@Case=1 AND convert(varchar(10),c.createdon,120)= CONVERT(VARCHAR(10),'2014-02-28 12:48:57.000',120)) 
   OR 
    (@Case=2 AND c.createdon <= '2014-06-01')
group by U.NETWORKID
order by U.NETWORKID

how to use switch statement with case in SQL

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us