Stored proc - Parameter issue
I have an SSRS combo box that will pass 1 of 3 values to my stored proc.
'ALL'
'NATIVE'
'LINKED'
I need the report to do the following based on one of these 3 options being picked.
If the parameter Native is picked...then it should be be equal to the columns value in the report of Native.
the same if Linked is chosen..
If All is chosen it should return anything in this column...which could be "Native" , "Linked", '' , NULL
Please help me build the where clause to handle this.
Here is what I have ..but its not working.
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
'ALL'
'NATIVE'
'LINKED'
I need the report to do the following based on one of these 3 options being picked.
If the parameter Native is picked...then it should be be equal to the columns value in the report of Native.
the same if Linked is chosen..
If All is chosen it should return anything in this column...which could be "Native" , "Linked", '' , NULL
Please help me build the where clause to handle this.
Here is what I have ..but its not working.
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
let me rephrase for clarity..
I have a parameter that will pass one of three values ( Linked, All, or Native"
All does not exists....All simply means both Linked and Native...but I also have to include blanks and nulls in the All as well so all records get returned on the All selection.. Another way to say is All means just ignore the parameter.
Chosing linked or native should return only rows where the column is equal to that.
I have a parameter that will pass one of three values ( Linked, All, or Native"
All does not exists....All simply means both Linked and Native...but I also have to include blanks and nulls in the All as well so all records get returned on the All selection.. Another way to say is All means just ignore the parameter.
Chosing linked or native should return only rows where the column is equal to that.
select @NativeLinked = case when @NativeLinked = 'All' then null else @NativeLinked end;
ASKER
This is a where clause...
And When the parameter passed is 'All' is should be every value...including nulls
This select you made reads to me when its all then make it null...else use the other value.
It should be when its all use null and all values...else use one or the other.
And When the parameter passed is 'All' is should be every value...including nulls
This select you made reads to me when its all then make it null...else use the other value.
It should be when its all use null and all values...else use one or the other.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I believe that it has been taken care of in suggested WHERE clause. If you believe not, please give scenario.
ASKER
hmmmmm..interesting...tryi
ASKER
Nitin..not following your use of numbers in your where clause
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
We are trying to arriving at the equality here and nothing more. Just to confuse people further, I even used to use different numbers everytime I write such case statements, for example:
where 99 = case .....then 99 else 98 end.
And typically developer keeps concentrating on 99 and 98 where they shouldn't. Hope you got it.
where 99 = case .....then 99 else 98 end.
And typically developer keeps concentrating on 99 and 98 where they shouldn't. Hope you got it.
ASKER
Thanks for the detailed solution....testing the solution worked....thanks im on an all nighter deadline:)
Welcome. Glad to help as always.
ASKER