February 5, 2009 at 5:02 am
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
February 5, 2009 at 5:59 am
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
February 5, 2009 at 6:06 am
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
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
February 5, 2009 at 6:11 am
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
February 5, 2009 at 6:46 am
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
February 5, 2009 at 6:53 am
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
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
February 5, 2009 at 6:59 am
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) )
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply