December 4, 2008 at 9:38 am
Hello All,
I am trying to setup a SP where by I would pass in an integer value and based on that value, I would want to perform a specific WHERE for my selection. Below you will find my Where Clause with my case statements. For some reason I cannot seem to make it work. I must be doing something logically wrong so if someone has any suggestions, I would be more than glad to hear them. I don't believe what I am trying to achieve is this hard but I can't seem to get it right.
Again, thanks for any ideas provided.
WHERE
CASE @Type
WHEN '1' THEN
[Tasks].ResponsibleDept = @Value-- BY DEPARTMENT
WHEN '2' THEN
[Tasks].TaskStatus = @Value-- BY STATUS
WHEN '3' THEN
DECLARE @EnterDate datetime
SELECT @EnterDate = CONVERT(datetime, @Value)
[Tasks].DateOfEntry Between @EnterDate + '00:00' AND @EnterDate + '23:59' -- BY ENTER DATE
WHEN '4' THEN
[Tasks].TaskRequestor Like @Value + '%'-- BY TASK ORIGINATOR
WHEN '5' THEN
[Tasks].CategoryCode = @Category-- BY TASK CATEGORY
WHEN '6' THEN
DECLARE @id int
SELECT @id = CONVERT(int, @Value)
[Tasks].ACMTaskID = @id-- BY TASK ID
END
December 4, 2008 at 9:48 am
Well you can't do this in a case:
WHEN '3' THEN
DECLARE @EnterDate datetime
SELECT @EnterDate = CONVERT(datetime, @Value)
[Tasks].DateOfEntry Between @EnterDate + '00:00' AND @EnterDate + '23:59' -- BY ENTER DATE
I think something like this would work better:
CASE @Type
WHEN '1' THEN
Case
When [Tasks].ResponsibleDept = @Value Then 1
Else 0
End -- BY DEPARTMENT
WHEN '2' THEN
Case
When [Tasks].TaskStatus = @Value Then 1
Else 0
End -- BY STATUS
WHEN '3' THEN
Case
When [Tasks].DateOfEntry Between @Value + '00:00' AND @Value + '23:59' Then 1
Else 0
End-- BY ENTER DATE
WHEN '4' THEN
Case
WHen [Tasks].TaskRequestor Like @Value + '%' Then 1
Else 0
End -- BY TASK ORIGINATOR
WHEN '5' THEN
Case
When [Tasks].CategoryCode = @Category Then 1
Else 0
End -- BY TASK CATEGORY
WHEN '6' THEN
Case
When [Tasks].ACMTaskID = Convert(Int, @value) Then 1
Else 0
End-- BY TASK ID
END = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 10:09 am
the CASE function can only be used to return a value. it can't be used to limit WHERE clause comparisons. also, you can't declare variables within a sql statement. you should declare your variables at the top of your stored proc and use one variable for each criteria.
declare @dept varchar(255), @status varchar(255), @date smalldatetime,
@requestor varchar(255), @category varchar(255), @taskId int
select @dept = @value where @Type = 1
select @status = @value where @Type = 2
select @date = cast(@value as smalldatetime) where @Type = 3
select @requestor = @value + '%' where @type = 4
select @category = @value where @type = 5
select @taskId = cast(@value as int) where @type = 6
...
WHERE
(
[Tasks].ResponsibleDept = @dept)
or [Tasks].TaskStatus = @status)
or ([Tasks].DateOfEntry >= @date and [Tasks].DateOfEntry < dateadd(day,1,@date))
or [Tasks].TaskRequestor Like @requestor)
or [Tasks].CategoryCode = @category)
or [Tasks].ACMTaskID = @taskId
)
December 4, 2008 at 10:46 am
Sorry I took so long to respond to your entries, I was away from my station. Jack and Antonio, I wish to thank you enormously for the time you both took to compose a solution. Both options works great. Also I thank you for your explanations of what I was doing wrong.
Have a great holiday season everyone.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply