Avatar of freedo55
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.
DB Reporting ToolsMicrosoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
natloz

8/22/2022 - Mon
frankytee

why not pass a parameter to another stored proc which in turn will execute the required stored proc based on that parameter?
frankytee

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
ASKER
freedo55

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
frankytee

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.
ASKER
freedo55

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?
frankytee

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
natloz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question