How to avoid multiple if else in sql server

Question

Post reply

How to avoid multiple if else in sql server

fanindrabhortakke

Old Hand

Points: 349
More actions
May 18, 2016 at 6:49 am

#316568

Hi,
I want to simplify the below code . Is it possible to write the below code using case statements or any alternate method.
DECLARE @a AS INT;
DECLARE @b-2 AS INT;
DECLARE @Parameter1 AS INT;
DECLARE @Parameter2 AS INT;
DECLARE @Parameter3 AS INT;
DECLARE @Parameter4 AS INT;
IF @a = 1
AND @b-2 = 0
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 1
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 0;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 2
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 0;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 3
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 4
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 0
AND @b-2 = 0
BEGIN
SET @Parameter1 = 68;
SET @Parameter2 = 14;
SET @Parameter3 = 0;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 0
AND @b-2 = 1
BEGIN
SET @Parameter1 = 78;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
END;
END;
END;
END;
END;
END;
Thanks in advance
Fanindra

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Something like this perhaps?

DECLARE @a AS INT;

DECLARE @b-2 AS INT;

DECLARE @Parameter1 AS INT;

DECLARE @Parameter2 AS INT;

DECLARE @Parameter3 AS INT;

DECLARE @Parameter4 AS INT;

select @Parameter1 = case @a when 1 then 77 else Case when @b-2 = 0 then 68 else 78 end end

SET @Parameter2 = 14;

select @Parameter3 = 21

where (@A = 1 and @b-2 in (0, 3, 4))

OR @a = 0 and @b-2 = 1

set @Parameter3 = ISNULL(@Parameter3, 0)

SET @Parameter4 = 0;

fanindrabhortakke (5/18/2016)
Hi,
I want to simplify the below code . Is it possible to write the below code using case statements or any alternate method.
DECLARE @a AS INT;
DECLARE @b-2 AS INT;
DECLARE @Parameter1 AS INT;
DECLARE @Parameter2 AS INT;
DECLARE @Parameter3 AS INT;
DECLARE @Parameter4 AS INT;
IF @a = 1
AND @b-2 = 0
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 1
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 0;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 2
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 0;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 3
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 1
AND @b-2 = 4
BEGIN
SET @Parameter1 = 77;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 0
AND @b-2 = 0
BEGIN
SET @Parameter1 = 68;
SET @Parameter2 = 14;
SET @Parameter3 = 0;
SET @Parameter4 = 0;
END;
ELSE
BEGIN
IF @a = 0
AND @b-2 = 1
BEGIN
SET @Parameter1 = 78;
SET @Parameter2 = 14;
SET @Parameter3 = 21;
SET @Parameter4 = 0;
END;
END;
END;
END;
END;
END;
END;
Thanks in advance
Fanindra

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 2

not sure what you are using the results for, but with so many values, i'd consider creatign a table with the permutations of the parameters you are filtering for.

maybe something like this?

DECLARE @a AS INT = 0;

DECLARE @b-2 AS INT = 1;

With ParameterMatrix (A,B,P1,P2,P3,P4)

AS

(

SELECT 1,0,77,14,21,0 UNION ALL

SELECT 1,1,77,14, 0,0 UNION ALL

SELECT 1,2,77,14, 0,0 UNION ALL

SELECT 1,3,77,14,21,0 UNION ALL

SELECT 1,4,77,14,21,0 UNION ALL

SELECT 0,0,68,14, 0,0 UNION ALL

SELECT 0,1,78,14,21,0

)

SELECT T1.* FROM SomeTable T1

INNER JOIN ParameterMatrix pm

ON pm.A = @a

AND pm.B = @b-2

AND T1.Col1 = pm.P1

AND T1.Col2 = pm.P2

ANd T1.Col3 = pm.P3

AND T1.Col4 = pm.P4

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!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Similar to what Lowell already posted, but leaving values as variables.

DECLARE @a AS INT = 1;

DECLARE @b-2 AS INT = 1;

DECLARE @Parameter1 AS INT;

DECLARE @Parameter2 AS INT;

DECLARE @Parameter3 AS INT;

DECLARE @Parameter4 AS INT;

SELECT @Parameter1 = Par1,

@Parameter2 = Par2,

@Parameter3 = Par3,

@Parameter4 = Par4

FROM( VALUES(1, 0, 77, 14, 21, 0),

(1, 1, 77, 14, 0, 0),

(1, 2, 77, 14, 0, 0),

(1, 3, 77, 14, 21, 0),

(1, 4, 77, 14, 21, 0),

(0, 0, 68, 14, 0, 0),

(0, 1, 78, 14, 21, 0))PM(A,B,Par1,Par2,Par3,Par4)

WHERE A = @a

AND B = @b-2;

SELECT @Parameter1 AS Par1,

@Parameter2 AS Par2,

@Parameter3 AS Par3,

@Parameter4 AS Par4;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2