How to use the Case in stored procedure

[sarang_k]

Hi all, I am using a SQL Server 2008 and created a procedure,but can any one tell me how to use Case in it. Thanks in advance.

[Abhishek Sur]

Case is used to implement IF - Else construct in a query. Use like this

Case <expression>
when 0 then <expression>
when 1 then <expression>
ELSE <expression>
END

Or use CASE WHEN <expression> = 0 THEN <expression> WHEN <expression> = 1 THEN <expression> ELSE <expression> END Therefore you can easily incorporate your logic around the select query using CASE structure. For instance : Select CASE WHEN AGE > 50 THEN 'AGED' ELSE 'YOUNG' END FROM RECORDS This will produce AGED if age is greater than 50 otherwise YOUNG. For detail see this : http://msdn.microsoft.com/en-us/library/aa258235(SQL.80).aspx[^] Cheers :rose:

Abhishek Sur **Don't forget to click "Good Answer" if you like this Solution.

---

My Latest Articles-->** Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

[Abhishek Sur]

If you dont want this in Select query.. inside your stored procedure.. rather you want to place a logic use If - Else instead. :thumbsup:

Abhishek Sur **Don't forget to click "Good Answer" if you like this Solution.

---

My Latest Articles-->** Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

[David Skelly]

Am I the only person in the world that uses Google when I'm trying to find information? 2 seconds on Google will give you approximately 14 million search results about this including some very good explanations of what CASE is and what it does and how to use it. Just type "sql server case" and away you go.

[Mycroft Holmes]

Some people seem to find it easier to type in a question rather than trawl through 14 million results! :laugh: :laugh: Ah I crack me up. Just because they can find excellent examples in the first 2-3 results is no reson not to be spoon fed. Send me the codz.

Never underestimate the power of human stupidity RAH

[Andy_L_J]

Mycroft Holmes wrote:

Send me the codz.

When you get them can you forward them to me? My homework is soooo important! :-D

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Niladri_Biswas]

Hi, don't know what is ur actual doubt because the problem stated

sarang_k wrote:

How to use the Case in stored procedure

is a normal thing.

If you are looking for syntax there is plenty in google with examples too. However, I have given 2 sample for the same program Sample 1:

Create procedure TestCaseStatement
as

Begin
declare @tbl table(id int)
insert into @tbl
select 0 union all select 1 union all
select 2 union all select 1 union all
select 1

select 
	id
	,case when id = 1 then 'One' else 'Other Number' **end as Value**	
 from @tbl

End

Sample 2:

Create procedure TestCaseStatement
as

Begin
declare @tbl table(id int)
insert into @tbl
select 0 union all select 1 union all
select 2 union all select 1 union all
select 1

select 
	id
	,**Value =** case when id = 1 then 'One' else 'Other Number' end 
 from @tbl

End

The output id Value

0 Other Number
1 One
2 Other Number
1 One
1 One

As you can see that I have shown 2 ways of holding the case result into a pseudo column. And as Mr. Mycroft stated, if you have any specific doubt, please don't hesitate to share it here. We all would love to help you. Hope you understand and ur doubt as per the question title has been answered. Have a nice day :)

Niladri Biswas