Curious about case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Curious about case statement

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-04 : 06:02:34

Hi guys,
Just I wondered is it possible to set a variable value upon the resultset of a case statement.
Example :-

Declare @Test as varchar
select case when Tbl.id='p01' then @Test='Y'
when Tbl.id='p02'then @Test='N'
end from Tbl

Any other alternatives...?

Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 06:07:53

you should do it like this:-
Declare @Test as varchar
select @Test=case when Tbl.id='p01' then 'Y'
when Tbl.id='p02'then 'N'
end from Tbl

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-02-04 : 06:13:07

[code]SELECT @Test = CASE Tbl.id
WHEN 'p01'
THEN 'Y'
WHEN 'p02'
THEN 'N'
END
FROM Tbl[/code]
You should also consider having an ELSE branch to catch any unexpected values.

Mark

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-04 : 06:15:20

Thanks Vishak.It worked..!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 06:18:02

1 Always specify column length for character datatypes http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx
2 It would return last row's value if the table has more than one value for that id

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-04 : 06:29:02

quote:
Originally posted by madhivanan

1 Always specify column length for character datatypes http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx
2 It would return last row's value if the table has more than one value for that id

Madhivanan

Failing to plan is Planning to fail

Yes Madhi what you say is true but in my condition the statement will return only a single row because the select statement has a where condition which I didnt added.I just needed to have the value in a variable rather than a row.
Anyways thank you all of them for your feedback.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources