convert CASE logic to IF

Question

convert CASE logic to IF

Robson Brandão

SSC-Addicted

Points: 413
More actions
April 13, 2011 at 12:10 pm

#389274

How Can I convert this code with IF structure ?
DECLARE @Offset SMALLINT
SELECT @Offset = CASE WHEN @Start_time = 'Daily' THEN null WHEN @Start_time =
'Weekly' THEN -7 WHEN @Start_time = 'Monthly' THEN -30
END
SELECT StartParam = CONVERT(datetime,DATEADD(dd,@Offset,GetDate()),103), EndParam =
CONVERT(datetime,GetDate(),103)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Robson Brandão (4/13/2011)
How Can I convert this code with IF structure ?
DECLARE @Offset SMALLINT
SELECT @Offset = CASE WHEN @Start_time = 'Daily' THEN null WHEN @Start_time =
'Weekly' THEN -7 WHEN @Start_time = 'Monthly' THEN -30
END
SELECT StartParam = CONVERT(datetime,DATEADD(dd,@Offset,GetDate()),103), EndParam =
CONVERT(datetime,GetDate(),103)

This is obviously a code snippet to a larger function/proc, but why would you want to convert it? What are you hoping to gain going to an IF/THEN/ELSE structure that you're not getting from the CASE function?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Robson Brandão SSC-Addicted Points: 413 More actions · Answer 2

For some reason, people want to use IF you put any other procedure, even if the performance goes down.

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 3

Like Craig said, this is not optimal.

But to use IFs, you could replace the Select statement with:

IF @Start_time = 'Daily'
SET @Offset = NULL
IF @Start_time = 'Weekly'
SET @Offset = -7
IF @Start_time = 'Monthly'
SET @Offset = -30

Edit > Craig, not Brandie

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Lowell SSC Guru Points: 323546 More actions · Answer 4

ok, i've seen people knee jerk on things like that...

You could mask the CASE statement as a SELECT (or JOIN it to your other data) like this:

/*--Results

(No column name)StartParamEndParam

DailyNULL2011-04-13 14:44:10.230*/

DECLARE @Start_time AS VARCHAR(30)

SET @Start_time = 'Daily'

SELECT

@Start_time,

StartParam = CONVERT(datetime,DATEADD(dd,Offset,GetDate()),103),

EndParam = CONVERT(datetime,GetDate(),103)

FROM (SELECT 'Daily' AS ST,NULL AS Offset UNION ALL

SELECT 'Weekly' AS ST, -7 UNION ALL

SELECT 'Monthly' AS ST, -30 ) X

WHERE @Start_time = ST

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Robson Brandão SSC-Addicted Points: 413 More actions · Answer 5

Robson Brandão

SSC-Addicted

Points: 413

April 13, 2011 at 12:53 pm

#1311265

thanks..