SQL 2005, IF THEN ELSE Results

Experts,
I am using the below IF Statement to evaluate a number of conditions and return 3 columns.
When run, it is returning both the desired result and the ELSE result. I want the ELSE, only if there is no matching IF loop.
What am I missing?

DECLARE @FR_CODE CHAR(4)
DECLARE @FR_Qty INT
DECLARE @TO_CODE CHAR(4)
DECLARE @TO_Qty INT
SET @FR_CODE = 'UNR'
SET @FR_Qty = -147
SET @TO_CODE = 'BLK'
SET @TO_Qty = 147


IF @FR_CODE = 'UNR' AND @TO_CODE = 'QI' 
	SELECT 
	'05' as W1901,
	@TO_QTY as W1902,
	'QI' as W1908
IF @FR_CODE = 'UNR' AND @TO_CODE = 'BLK' 
	SELECT 
	'AH' as W1901,
	@FR_QTY as W1902,
	'UNR' as W1908
IF @FR_CODE = 'UNR' AND @TO_CODE = 'DIS' 
	SELECT 
	'BS' as W1901,
	@FR_QTY as W1902,
	'UNR' as W1908
IF @FR_CODE = 'QI' AND @TO_CODE = 'UNR' 
	SELECT 
	'AX' as W1901,
	@TO_QTY as W1902,
	'UNR' as W1908
IF @FR_CODE = 'QI' AND @TO_CODE = 'BLK' 
	SELECT 
	'AH' as W1901,
	@FR_QTY as W1902,
	'QI' as W1908
IF @FR_CODE = 'QI' AND @TO_CODE = 'DIS' 
	SELECT 
	'AU' as W1901,
	@FR_QTY as W1902,
	'QI' as W1908
IF @FR_CODE = 'BLK' AND @TO_CODE = 'UNR' 
	SELECT 
	'52' as W1901,
	@TO_QTY as W1902,
	'UNR' as W1908
IF @FR_CODE = 'DIS' AND @TO_CODE = 'UNR' 
	SELECT 
	'BR' as W1901,
	@TO_QTY as W1902,
	'UNR' as W1908
ELSE 
	SELECT
	'XX' as W1901,
	'55' as W1902,
	'XX' as W1908

Open in new window

LVL 1
JDCamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The ELSE is only for the last IF. You might want to transform all those IF conditions in a single IF block:
DECLARE @FR_CODE CHAR(4)
DECLARE @FR_Qty INT
DECLARE @TO_CODE CHAR(4)
DECLARE @TO_Qty INT
SET @FR_CODE = 'UNR'
SET @FR_Qty = -147
SET @TO_CODE = 'BLK'
SET @TO_Qty = 147


IF @FR_CODE = 'UNR' AND @TO_CODE = 'QI' 
	SELECT 
	'05' as W1901,
	@TO_QTY as W1902,
	'QI' as W1908
ELSE IF @FR_CODE = 'UNR' AND @TO_CODE = 'BLK' 
	SELECT 
	'AH' as W1901,
	@FR_QTY as W1902,
	'UNR' as W1908
ELSE IF @FR_CODE = 'UNR' AND @TO_CODE = 'DIS' 
	SELECT 
	'BS' as W1901,
	@FR_QTY as W1902,
	'UNR' as W1908
ELSE IF @FR_CODE = 'QI' AND @TO_CODE = 'UNR' 
	SELECT 
	'AX' as W1901,
	@TO_QTY as W1902,
	'UNR' as W1908
ELSE IF @FR_CODE = 'QI' AND @TO_CODE = 'BLK' 
	SELECT 
	'AH' as W1901,
	@FR_QTY as W1902,
	'QI' as W1908
ELSE IF @FR_CODE = 'QI' AND @TO_CODE = 'DIS' 
	SELECT 
	'AU' as W1901,
	@FR_QTY as W1902,
	'QI' as W1908
ELSE IF @FR_CODE = 'BLK' AND @TO_CODE = 'UNR' 
	SELECT 
	'52' as W1901,
	@TO_QTY as W1902,
	'UNR' as W1908
ELSE IF @FR_CODE = 'DIS' AND @TO_CODE = 'UNR' 
	SELECT 
	'BR' as W1901,
	@TO_QTY as W1902,
	'UNR' as W1908
ELSE 
	SELECT
	'XX' as W1901,
	'55' as W1902,
	'XX' as W1908

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Please use set-based approaches where possible:

DECLARE @FR_CODE CHAR(4); SET @FR_CODE = 'UNR';
DECLARE @FR_Qty INT; SET @FR_Qty = -147;
DECLARE @TO_CODE CHAR(4); SET @TO_CODE = 'BLK';
DECLARE @TO_Qty INT; SET @TO_Qty = 147;
DECLARE @W1901 VARCHAR(4); SET @W1901 = 'XX';
DECLARE @W1902 INT; SET @W1902 = 55;
DECLARE @W1908 VARCHAR(4); SET @W1908 = 'XX';

DECLARE @Values TABLE
    (
      FromCode VARCHAR(4) ,
      ToCode VARCHAR(4) ,
      W1901 VARCHAR(4) ,
      W1902 INT ,
      W1908 VARCHAR(4)
    );

INSERT  INTO @Values
VALUES  ( 'UNR', 'QI', '05', @TO_Qty, 'QI' ),
        ( 'UNR', 'BLK', 'AH', @FR_Qty, 'UNR' ),
        ( 'UNR', 'DIS', 'BS', @FR_Qty, 'UNR' ),
        ( 'QI', 'UNR', 'AX', @TO_Qty, 'UNR' ),
        ( 'QI', 'BLK', 'AH', @FR_Qty, 'QI' ),
        ( 'QI', 'DIS', 'AU', @FR_Qty, 'QI' ),
        ( 'BLK', 'UNR', '52', @TO_Qty, 'UNR' ),
        ( 'DIS', 'UNR', 'BR', @TO_Qty, 'UNR' );

SELECT  @W1901 = V.W1901 ,
        @W1902 = V.W1902 ,
        @W1908 = V.W1908
FROM    @Values V
WHERE   V.FromCode = @FR_CODE
        AND V.ToCode = @TO_CODE;

SELECT  @FR_CODE AS FR_CODE ,
        @TO_CODE AS TO_CODE ,
        @W1901 AS W1901 ,
        @W1902 AS W1902 ,
        @W1908 AS W1908;

Open in new window

0
JDCamAuthor Commented:
Got it!  Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.