Click here to Skip to main content
15,920,438 members
Please Sign up or sign in to vote.
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
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???
[no name] 18-Apr-14 5:10am    
Thank you so much For the Reply.Its Done.

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
 
Share this answer
 
Comments
[no name] 18-Apr-14 5:09am    
Thank you so much For the Reply.
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
 
Share this answer
 
Comments
[no name] 18-Apr-14 5:09am    
Thank you Sir.
This is easy .
Thank You
 
Share this answer
 
Comments
[no name] 18-Apr-14 5:10am    
Thank you so much For the Reply.
Google it..

CASE (Transact-SQL)
 
Share this answer
 
Comments
[no name] 18-Apr-14 5:10am    
Thank you so much For the Reply.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900