simple case select question - 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)

simple case select question

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-09-02 : 11:48:27

DECLARE @mgaindicator CHAR(3)

SELECT @mgaindicator = CASE Cast(@company + '' + @subcompany AS CHAR(2))
WHEN '10'
THEN '590'
WHEN '33'
THEN '000'
END

@company and @subcompany are single digit ints. Getting null when it should be 000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 11:51:04

may be this:-


DECLARE @mgaindicator CHAR(3)

SELECT @mgaindicator = CASE Cast(isnull(@company + '','') + isnull(@subcompany,'') AS CHAR(2)) 
WHEN '10'
THEN '590'
WHEN '33'
THEN '000'
END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-09-02 : 11:52:39

Also try to handle default case in CASE statement

For e.g.

DECLARE @mgaindicator CHAR(3)

SELECT @mgaindicator = CASE Cast(isnull(@company + '','') + isnull(@subcompany,'') AS CHAR(2)) 
WHEN '10'
THEN '590'
WHEN '33'
THEN '000'
ELSE ''
END

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 11:53:01

convert the ints before you try to manipulate them as strings:

cast(@company as char(1)) + cast(@subCompany as char(1)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources