Using: CASE, ISNULL(), NULLIF(), COALESCE()

Question

Post reply

Using: CASE, ISNULL(), NULLIF(), COALESCE()

Van Heghe Eddy

SSCarpal Tunnel

Points: 4449
More actions
February 5, 2009 at 5:02 am

#383875

Hi,
Could someone help me in the right direction with this problem:
Problem: I need to select some data from one table but depending on a parameter is have to select them all or just one type
DECLARE @var INT
SET @var =5
SELECT * FROM [BASISREL] AS b
WHERE PAR_ID_FIRMA = @FirmaId AND
(
[PAR_ID_TYPE] = COALESCE(@var,999)
OR
[PAR_ID_TYPE] >= COALESCE(NULLIF(0,@var),999)
)
What it should do is when @var is < 20 then select only those rows where the [PAR_ID_TYPE] = @var
else
select ALL rows from the table that are from the selected Firma so technicaly bypassing the PAR_ID_TYPE clause
Can this be done?
Tnx for all help
wkr,
Eddy

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

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

Vic.K SSC Eights! Points: 970 More actions · Answer 1

Why don't you use IF Statement?

DECLARE @var INT

DECLARE @ID_MIN INT

DECLARE @ID_MAX INT

SET @var = 5

IF @var < 20

BEGIN

SET @ID_MIN = @var

SET @ID_MAX = @var

END

ELSE

BEGIN

SET @ID_MIN = your min value

SET @ID_MAX = your max value

END

SELECT * FROM [BASISREL] AS b

WHERE PAR_ID_FIRMA = @FirmaId AND

[PAR_ID_TYPE] BETWEEN @ID_MIN AND @ID_MAX

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT * FROM [BASISREL] AS b

WHERE PAR_ID_FIRMA = @FirmaId

AND [PAR_ID_TYPE] = CASE WHEN @var < 20 THEN @var ELSE [PAR_ID_TYPE] END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 3

Hi Vic,

Was been thinking on that also as solution,

but as i have to get this for multiple variables it does make the code messy

and therefore i was trying whether there is no alternative with the sql functions:

Case,

Isnull

Nullif

Coalesce

But i do not now whether you can use 'functions' to manipulate the where clause without using dynamic SQL

So thats what i am trying to figure out,

In BOL i can not find whether its possible but neither do i find that it is not possible.

Wkr,

Eddy

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 4

Hi Chris,

Tnx, This is what i was looking for,

Strange but i had been trying that in my experiments and got the error on the "<"

As i try your code now, it works like a charm,

Probably i must have been having a typo or something.

Tnx a lot for the example,

Wkr,

Eddy

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Thanks for the feedback eddy. Please be advised that this type of construction can cause performance problems if the cached plan is a poor match for the parameters received when the sproc is run.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

NotManyPoints SSC Eights! Points: 808 More actions · Answer 6

Just going by you initial question:

What it should do is when @var is < 20 then select only those rows where the [PAR_ID_TYPE] = @var
else
select ALL rows from the table that are from the selected Firma so technicaly bypassing the PAR_ID_TYPE clause

SELECT * FROM [BASISREL] AS b

WHERE PAR_ID_FIRMA = @FirmaId AND

( (@var < 20 AND PAR_ID_TYPE = @var) OR (@var >= 20) )