Stored Procedures in Crystal Reports


Just a general question we are looking at using stored procedures and using them instead of SQL commands just wandered what the advantages/disadvantages of doing this - i.e. for such things as parameters, performance or anything else?
Mark WilsonBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ganesh GuruduSenior ConsultantCommented:
Stored Procedures are secured and faster then normal SQL command. it reuse the explain plan and pre-complied code.

Direct SQL Command are can be hacked and rad parsing. compared to Sp bit slower. You can also refer the below URL for reference.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
Always go with the Stored procedures rather than Adhoc SQL.

Stored procedures are better for performance as they retain the execution plan and reuses it for the next execution.

Stored procedures are precompiled and cannot errored out which can happen with sql.

Also if need some changes that you can do in Sp directly rather than change in sql and the report.
The comments above are good and correct.

How do you plan to use  them in Crystal?
Are you going to just pull them in as you would tables or will you execute them through the COMMAND interface?

Depending on your access, stored procedures can be more difficult to change.  Not from a writing it but access.  Some clients I worked for didn't provide database access so if I needed a SP or query changed I had to request it which then took more time, sometimes several days.  Others gave me full access so I could build my own.

SQL errors are easier to track down and fix in an SP since the error messages from a COMMAND are cryptic at best and generally don't provide any real clue as to the problem.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.