October 12, 2005 at 9:30 am
Hey guys-
Im trying to run this query:
SELECT ZEVTECSNO, ZEVTECSYR, ZEVTOF017, ZEVTCADIS, ZEVTCALTP
FROM RMSPRDF.ZEVT
WHERE (ZEVTDTRPT BETWEEN ? AND ?) AND (ZEVTOF017 <> 0) AND (ZEVTOF017 = ?)
ORDER BY ZEVTCADIS
I am trying to allow for a null value for a the parameter that corresponds to ZEVTOF017 so that the query that would run would be:
SELECT ZEVTECSNO, ZEVTECSYR, ZEVTOF017, ZEVTCADIS, ZEVTCALTP
FROM RMSPRDF.ZEVT
WHERE (ZEVTDTRPT BETWEEN ? AND ?) AND (ZEVTOF017 <> 0)
ORDER BY ZEVTCADIS
So basically if a user doesnt specify a paramter for ZEVTOF017 then dont use it in the query.
How do I accomplish this?
Thanks,
Jon
October 12, 2005 at 9:38 am
In reporting services, You going to have to embed your query in a stored procedure and either have if then else logic to decide which query to run, or use dynamic sql.
October 12, 2005 at 10:03 am
what would be easier, using a stored procedure or dynamic SQL?
I need a little clarification.
Thanks Ray.
October 12, 2005 at 11:33 am
In either case you'll need to use a stored procedure.
and you can structure your stored procedure 2 ways.
1 way is to evaluate the parameters passed in.
If @param1 is null, and @param2 is not null
begin
Query here ...
end
Else if Param1 is not null and param2 is null
begin
query here
end
else....
Or use dynamic sql to prepare your query.
October 12, 2005 at 1:03 pm
What would be easier using stored procedures or preparing with dynamic SQL?
Jon
October 12, 2005 at 9:02 pm
For the parameter ZEVTOF017 do you populate a drop-dwon for the user or they have to type a value in? If you populated the list, you could always add an extra list member, sorted to the top of the list, something like 'ALL' or perhaps 'No selection'. Assuming that the keys for this value are positive integers, you could set the key for this added value to -1, then you could then change your original query to say ...
WHERE ...... (ZEVTOF017 = @MyParam OR @MyParam = -1)
If the user selected the 'ALL' value, then that part of the where clause would effectively be ignored.
Steve.
October 13, 2005 at 2:40 pm
I do something like this:
select distinct User_Dim.Customer_Group_name as Customer_Group_Name, User_Dim.Customer_Group_Name as label
from User_dim with (NOLOCK)
where not(Customer_Group_Name = '')
union
SELECT 'All' as ID, ' All' as Label
order by label
this gives me the group names I want PLUS I get an extra label that always sits at the top of the dropdown list. Then:
select .........and (Customer_Group_Name=@group or @group = 'All')
October 27, 2005 at 12:43 pm
How do you handle this process when you have unnamed parameters (ODBC uses the generic '?' for each parameter and you can't re-use a parameter in a query)? MySQL only has stored procedures available for 5.0 onwards - we are still using 4.1.13 and need ODBC for the datasources.
Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply