April 8, 2011 at 12:34 pm
Hello all,
I have a stored proc, and I am trying to add some code to the where clause.
The following is a snippet of what I want to do. It's mainly the case statment in the where clause.
select *
from blah
where
blah = blah
and blah = blah
and
((
case
when @ActivityStatusID = 1
then ACS.[Description] = 'Completed'
when @ActivityStatusID <> 1
then ACS.[Description] <> 'Completed'
)
Thanks for any help or advise.
KS.
April 8, 2011 at 12:41 pm
is @ActiveStatusID a parameter or a variable. and is it defined in the stored procedure. What you have look good to me other than you need to add "End" after the last when. you could also change the last when to an else if you would like so you would get something like this.
Case
When @ActiveStatusID=1 then Col1='Completeled'
else Col1='Not completed'
end
sorry I know that was not your exact text but it should give you the idea.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 12:48 pm
@ActivityStatusID is a paramater I am passing in.
If it is a 1 then I want to:
Select all where ... AND ACS.Description = 'Completed' --gets all that are completed
OR select all where... ACS.Description <> 'Completed' -- does not = completed.
ACS.Description is a field that gets added to my proc. through an inner join.
April 8, 2011 at 12:51 pm
this should do the trick I would think.
case
when @ActivityStatusID = 1 then ACS.[Description] = 'Completed'
else ACS.[Description] <> 'Completed'
end
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 12:54 pm
Sorry but no joy, I get Incorrect syntax near '='. Samething for the else
April 8, 2011 at 12:55 pm
and
((
case
when @ActivityStatusID = 1
then ACS.[Description] = 'Completed'
when @ActivityStatusID <> 1
then ACS.[Description] <> 'Completed'
)
or @ActivityStatusID is null)
END
I still get Msg 102, Level 15, State 1, Line 369
Incorrect syntax near '='.
April 8, 2011 at 12:58 pm
Now above that I have
And ((
Case
when @SoldYN = 'Y'
then (
(SELECT COUNT(Distinct(A2.ParentActivityID))
FROM Activities A2
INNER JOIN FormInstances FI2 ON FI2.ActivityID = A2.ActivityID
INNER JOIN FormInstanceProposals FIP2 ON FIP2.FormInstanceID = FI2.FormInstanceID
WHERE A2.ParentActivityID = A.ActivityID
AND A.ActivityStatusID = 'CPL'
and FIP2.Sold='Y'
))
when @SoldYN = 'N'
then (
(SELECT COUNT(Distinct(A2.ParentActivityID))
FROM Activities A2
INNER JOIN FormInstances FI2 ON FI2.ActivityID = A2.ActivityID
INNER JOIN FormInstanceProposals FIP2 ON FIP2.FormInstanceID = FI2.FormInstanceID
WHERE A2.ParentActivityID = A.ActivityID
AND A.ActivityStatusID = 'CPL'
and FIP2.Sold='N'
))
End) =1 or @SoldYN is null)
which works great. Again @SoldYN is a param I pass in of 'Y' or 'N'
April 8, 2011 at 12:58 pm
what type is @ActivityStatusID defined as?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 1:00 pm
nvarchar(3000) I made it that big because I've been testing all sorts of things. I've tried using an IN rather than = <>.
April 8, 2011 at 1:01 pm
that is your problem then try @activityStatusID='1'
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 1:04 pm
Sorry still no joy, tried that.
I also verified that ASC.Description is a nvarchar 80 so that's what my @ActivityStatusID is now.
and
((
case
when @ActivityStatusID = '1'
then ACS.[Description] = 'Completed'
when @ActivityStatusID <> '1'
then ACS.[Description] <> 'Completed'
)
or @ActivityStatusID is null)
END
Still does not like the = in the case statment.
April 8, 2011 at 1:27 pm
You have a couple choices to make this work. The case statement in a where clause the way you trying isn't going to work. If the proc is pretty small you could just build two select statements in it each with the appropriate where clause.
if @ActivityStatusID = 1 then
...first select here
where ACS.[Description] = 'Completed'
else
..second select here
where ACS.[Description] <> 'Completed'
Otherwise you are looking at dynamic sql.
_______________________________________________________________
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/
April 8, 2011 at 1:57 pm
Sorry no joy, I tried that.
Also the ACS.Description fields is a nvarchar(80) which is what I set @ActiveStatusID to.
I tried this, and it sort of works, but get a Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
and ACS.[Description] in(
case when @ActivityStatusID = 0
then 'Completed'
--(select [Description] from ActivityStatuses
--where [Description] = 'Completed')
--'Completed'
else (
select [Description] from ActivityStatuses
where [Description] <> 'Completed')
--ACS.[Description] <> 'Completed'
end
)
April 8, 2011 at 1:58 pm
I have tried this, but get a
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
and ACS.[Description] in(
case when @ActivityStatusID = 0
then 'Completed'
--(select [Description] from ActivityStatuses
--where [Description] = 'Completed')
--'Completed'
else (
select [Description] from ActivityStatuses
where [Description] <> 'Completed')
--ACS.[Description] <> 'Completed'
end
)
April 8, 2011 at 2:06 pm
Of course that subquery returned more than 1 value. If can post some ddl, sample data and exactly what you are tying to do I will help.
I don't understand that you say you tried using two completely distinct queries but the code you showed had stuff all mixed together.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply