Can I use Case statement like this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Can I use Case statement like this?

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-03-02 : 08:13:07

Hi

I am trying to call a function multiple times as I want to display records depending on the returned results in the query, and depending on the value of a column being null in the returend results. So what I want to do is this:


SELECT ColumnA,
CASE WHEN ColumnA IS NULL THEN
dbo.Function(ColumnB, 'First') AS 'A - First',
dbo.Function(ColumnB, 'Second') AS 'A - Second',
dbo.Function(ColumnB, 'Third') AS 'A - Third'
ELSE
dbo.Function(ColumnB, 'First') AS 'B - First',
dbo.Function(ColumnB, 'Second') AS 'B - Second',
dbo.Function(ColumnB, 'Third') AS 'B - Third'
END,
ColumnC,
ColumnD
From .....

but keep getting the following error when I run it:

quote:
Incorrect syntax near the keyword 'AS'.

This is for the first line that calls the dbo function.

Is the syntax wrong somewhere or am I not using it right?

G

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-02 : 08:41:38

You cant use case in this manner.
As per your requirement you can try this -


SELECT ColumnA,
CASE WHEN ColumnA IS NULL THEN dbo.Function(ColumnB, 'First') END AS 'A - First',
CASE WHEN ColumnA IS NULL THEN dbo.Function(ColumnB, 'Second') END AS 'A - Second',
CASE WHEN ColumnA IS NULL THEN dbo.Function(ColumnB, 'Third') END AS 'A - Third'
CASE WHEN ColumnA IS NOT NULL THEN dbo.Function(ColumnB, 'First') END AS 'B - First',
CASE WHEN ColumnA IS NOT NULL THEN dbo.Function(ColumnB, 'Second') END AS 'B - Second',
CASE WHEN ColumnA IS NOT NULL THEN dbo.Function(ColumnB, 'Third') END AS 'B - Third',
ColumnC,
ColumnD
From .....

Vaibhav T

If I cant go back, I want to go fast...

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-03-02 : 09:03:52

Thanks for your reply and I see how to do it now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources