CODEWITHSUNDEEP
sql
Emre801
Emre801

Reputation: 3203

Case statements in SQL

Hello I'm new to SQL and I want to use the Case statement.

I currently have this SQL statement 

update tblAccount set FullName = rtrim(isnull(FirstName,''));
update tblAccount set FullName = FullName + ' ' + rtrim(MI)
where substring(reverse(MI),1,1)='.';
update tblAccount set FullName = FullName + ' ' + rtrim(MI) + '.'
where substring(reverse(MI),1,1)!='.'
and MI is not null and len(rtrim(MI)) = 1;
update tblAccount set FullName = FullName + ' ' + rtrim(MI) 
where len(rtrim(MI)) >= 2;
update tblAccount set FullName = FullName + ' ' + LastName;
update tblAccount set FullName = FullName + ', ' + Suffix 
where Suffix is not null
and len(rtrim(Suffix)) > 0;

I want to convert this into something smaller and more easily readable, I was told a Case statement might help but I'm familar with it, I was wondering if anyone would be able to help convert this into something more readable.

Upvotes: 0

Views: 185

Answers (3)

jos
jos

Reputation: 823

The general form is

CASE WHEN <condition> THEN <value> ELSE <somethingElse> END

Where WHEN <condition> THEN <value> can be repeated and ELSE <somethingElse> is optional

A case statement can be used a a control structure, as in stored procedures or a script, or inline, as in a SELECT.

Your update statements are equivalent to this:

UPDATE tblAccount SET FullName = 
    rtrim(isnull(FirstName,''))
    + CASE 
        WHEN substring(reverse(MI),1,1)='.' THEN ' ' + RTRIM(MI)  
            + CASE WHEN where len(rtrim(MI)) >= 2 THEN RTRIM(MI) END
        WHEN MI is not null and len(rtrim(MI)) = 1 THEN rtrim(MI) + '.'
            + CASE WHEN where len(rtrim(MI)) >= 2 THEN RTRIM(MI) END
        ELSE
            + CASE WHEN where len(rtrim(MI)) >= 2 THEN RTRIM(MI) END
    END
    + ' ' + LastName
    + CASE 
        WHEN Suffix IS NOT NULL AND len(rtrim(Suffix)) > 0 THEN ', '+Suffix 
    END

You had 6 updates, nrs 1, 5 and 6 are straightforward, they are pretty much copied into the one statement above. Records updates by 2 will not be touched by 3, and 4 can (according to your code, data may prove otherwise) occur with 2, 3 or neither, hence 4's repetition.

Naturally, I wholeheartedly disagree with the thesis that your statements are just fine. If what you need is one update, than do one update. And don't give in to the pressure by accepting a non-answer either.

Have fun!

have a look at COALESCE() too, while you're learning.

Upvotes: 0

Hector Sanchez
Hector Sanchez

Reputation: 2317

I would rewrite it like this, just a little upgrade in performance, But i think it's more readable in the way that you have it, like @Bohemian said. 

--MIDDLE NAME?
    update tblAccount 
    set FullName = 
                    case when  substring(reverse(MI),1,1)='.' THEN --  FIRST + SECOND UPDATE
                        rtrim(isnull(FirstName,'')) + ' ' + rtrim(MI)
                    case when substring(reverse(MI),1,1)!='.' and MI is not null and len(rtrim(MI)) = 1 THEN --FIRST AND THIRD UPDATE
                        rtrim(isnull(FirstName,'')) + ' ' + rtrim(MI) + '.'
                    else  -- FIRST UPDATE
                         rtrim(isnull(FirstName,''));
                    end 

    GO      
    -- LASTNAME     
    update tblAccount 
    set FullName = case when len(rtrim(MI)) >= 2 THEN
                        (FullName + ' ' + rtrim(MI)) + ' ' + LastName
                    else
                        FullName + ' ' + LastName;
                    END
    GO

    -- SUFFIX
    update tblAccount 
    set FullName = FullName + ', ' + Suffix 
    where Suffix is not null
    and len(rtrim(Suffix)) > 0;

Upvotes: 1

Bohemian
Bohemian

Reputation: 424983

It could be made into one statement, but because your code re-modifies the same column, you would have to nest and combine function calls and it would be an absolutely huge statement and thus far less readable than your current code.

Also, the order of execution is important, so maintaining and debugging a single statement would be much more difficult.

Leave it as it is. What you have is good.


Regarding performance, may I say that maintenance and clarity of code outweigh performance every time (as long as the performance is "acceptable").

That said, your current calls each require a full table scan, where as a single statement would require only one. However, depending on the size of your table, after the first scan the table may be cached in memory, and the single statement may be so complex that it is actually slower than your current code.

You would have to test it, but you shouldn't consider performance unless there's a problem - don't optimize your code early.

Upvotes: 2

Related Questions