Stored Procedures

on
What is Stored Procedures?
Stored Procedures is SQL code that can save in SQL server. Stored Procedures code can reuse over and over again.
so if you use any sql code many time then use Stored Procedures.

Syntax

CREATE PROCEDURE tored_procedures_name>
AS
sql code.....
GO;


Execute Syntax

 EXEC tored_procedures_name> <parameter1>,<parameter2>,<parameter3>

 

Pros

  • Performance:  the ability for the stored procedure to reuse a query plan (stored procedure cache)
  • Can reuse the same stored procedure for more than one report (may minimize errors or duplication)
  • DBA can tune more effectively, if needed
  • Permits the DBMS to secure the object, if needed
  • Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them
  • Provides a layer of abstraction between the database tables & the report (for example: you can alias column names, create derived fields, minimize the effect of other physical object changes, or show less fields to simplify the reporting process)
  • Provides an additional layer of security since "Execute" permissions are required for either the user running the report, or an impersonation account
  • Ability to query system tables to find usage of tables & columns (which may help with change management)
  • For a minor change, permits the ability to alter the stored procedure without requiring the RDL to be redeployed

Cons

  • Need “Create” permissions on the source database (as an outside consultant, this is not always granted), or need another person to create the stored procedure for you
  • Slightly more knowledge is required to create a stored procedure than a simple select statement
  • Can clutter up the database with quite a few simple queries and/or redundant queries
  • Additional handling is needed to parse multi-valued parameters in SSRS
  • Two-step deployment (the stored procedure, and/or the RDL); this creates an opportunity for error if not deployed concurrently
  • Additional testing of a changed stored procedure & the effect of the change on the report (which may take slightly more time since they are separate)
  • May require additional personnel / time / coordination of efforts if the stored procedures are maintained & enhanced by staff other than the reports (for example, if a field changes, or a new parameter is requested)

 


 


Comments

Post a Comment