freedo55
asked on
How can I conditionally call a stored procedure in RS2005 when using a command type of 'Stored Proc'?
Hello All,
I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure, it runs fine. But if I try to use a =IIF(condition, if true run stored proc 1, if false run storedproc 2) but the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.
I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure, it runs fine. But if I try to use a =IIF(condition, if true run stored proc 1, if false run storedproc 2) but the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.
frankytee
why not pass a parameter to another stored proc which in turn will execute the required stored proc based on that parameter?
for eg:
create proc spCallStoredProc (@ipRS as int)
as
begin
if @ipRS = 1
begin
exec sp_Proc1 'whatever
end
if @ipRS = 2
begin
exec sp_Proc2 'whatever
end
end
etc
create proc spCallStoredProc (@ipRS as int)
as
begin
if @ipRS = 1
begin
exec sp_Proc1 'whatever
end
if @ipRS = 2
begin
exec sp_Proc2 'whatever
end
end
etc
ASKER
Our DBA prefers not to have conditional coding like that in the SP hence why we need to do it at the presentation layer, aka the Report. Can I perform this same step using a command type of stored proc within RS 2005?
i dont have RS on my pc so I couldn't verify. In my opinion DBA's should not have the authority to place unrealistic constraints on the developer. The restrictions from your DBA is typical of a DBA with no coding or development background. Conditional coding is part of the real world, especially as stored procs that drive reports are fundamentally "read only" as they return select statements rather than update transaction data.
if you can't find a solution in RS in a timely manner, then if i were you i'll chat with your manager if the DBA won't budge.
if you can't find a solution in RS in a timely manner, then if i were you i'll chat with your manager if the DBA won't budge.
ASKER
Already discussed and dismissed. The DBA has full authority and backing for those constraints. We have to find an alternative. This 'practice' has been in place long before I arrived and will not change anytime soon. Having said that, is what I'm asking possible?
Unfortunately I have not used Reporting Services for over 3 years and I dont have RS on my pc so I couldn't explore nor remember what was available.
but what i do remember was using the conditional stored proc that i posted (which your dba won't allow) as it was the most timely solution at that time.
one possible solution: show the limitations of RS to your DBA and ask him/her how it should be done since your company infers he/she is omniscient
but what i do remember was using the conditional stored proc that i posted (which your dba won't allow) as it was the most timely solution at that time.
one possible solution: show the limitations of RS to your DBA and ask him/her how it should be done since your company infers he/she is omniscient
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.