February 19, 2013 at 4:25 am
I am trying to select different data from a table depending on what the passed parameter value is. If the parameter is 0 then select all products from plu_file_dates with ToDept value of 60 in the plu_file or else select all products with ToDept of 60 and FromDept with value matching parameter.
However no matter how I try to write the where statement it always fails to execute
DECLARE @Dept AS INT
SET @Dept = 40
SELECT * FROM plu_file_dates
WHERE pluid in (SELECT pluid FROM plu_file WHERE (CASE WHEN @Dept = 0 THEN (ToDept = 60) ELSE (FromDept = @Dept and ToDept = 60) END))
February 19, 2013 at 4:36 am
You can fix the syntax like this: -
DECLARE @Dept AS INT
SET @Dept = 40
SELECT *
FROM plu_file_dates
WHERE pluid IN (SELECT pluid
FROM plu_file
WHERE ToDept = 60 AND FromDept = CASE WHEN @Dept = 0
THEN FromDept
ELSE @Dept END
);
However, this isn't the best way for the query optimiser to work, see Gail's blog post here --> http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D
Instead, do something like this: -
DECLARE @Dept AS INT;
SET @Dept = 40;
IF @Dept = 0
BEGIN
SELECT *
FROM plu_file_dates
WHERE pluid IN (SELECT pluid
FROM plu_file
WHERE ToDept = 60
);
END
ELSE BEGIN
SELECT *
FROM plu_file_dates
WHERE pluid IN (SELECT pluid
FROM plu_file
WHERE ToDept = 60 AND FromDept = @Dept
);
END
February 19, 2013 at 4:41 am
it think the way you have written the query is not right
case statement should be the part of the expression
in your case you can use case like this
select * from sys.objects
where name=(case when name='t1 ' then 'condition '
else 'condition2'
end)
February 19, 2013 at 4:46 am
Thanks, used the second solution and works perfectly
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply