Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

32

The query as it stands, now including ServiceTypeID as a value in the SELECT clause and not as

criteria, is ready to begin its life as a stored procedure. Queries serve many purposes, one of which is the

development of reports, as you’ll do in Chapter 6. However, encapsulating queries in stored procedures

is typically the preferred method of deployment for several reasons. Stored procedures, like ad hoc

queries, execute according to the execution plan generated by the query optimizer. Having the ability to

reuse the execution plan saves time and resources. Stored procedures, which are also beneficial because

they’re precompiled, can reuse an execution plan even though the parameters passed to it at execution

time might have changed values. You can hold stored procedures centrally on the SQL Server machine,

unlike ad hoc queries that might be embedded in an application (or in the RDL file in this case). When

the underlying schema of a database changes, you can update the stored procedure in one location,

whereas embedded queries all need to be modified separately for each report in which they reside. In

the next section, we’ll show you how to create a stored procedure based on the employee cost query.

Using a Parameterized Stored Procedure

You can use SSMS to produce the code to create a stored procedure based on the employee cost query,

and to drop it if the stored procedure already exists in the database. To create a stored procedure,

expand the database where you want to create the stored procedure, navigate to the Programmability

folder and expand it to see a folder named Stored Procedures. Right-click on that folder and choose,

New Stored Procedure. This opens a window containing a sample CREATE PROCEDURE command for the

new stored procedure.

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

To complete the new stored procedure, which you should name Emp_Svc_Cost, you simply need to

paste in your SELECT statement. However, you can add optional parameters to limit the result set based

on the following criteria:

• Service time (year and month)


• The branch where the employee works


• The individual employee


• The type of service

To create parameters for a stored procedure, you add the variable names, each prefixed by an @

character and provide the appropriate data types along with their default values if desired. The default

values for all the parameters are set to NULL, as Listing 2-4 shows. You can find the code for this query in

the code download file CreateEmpSvcCost.sql in the SQL Queries folder.

Listing 2-4. Creating the Emp_Svc_Cost Stored Procedure

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'Emp_Svc_Cost'

AND ROUTINE_SCHEMA = 'dbo')

DROP PROCEDURE dbo.Emp_Svc_Cost

GO

CREATE PROCEDURE [dbo].[Emp_Svc_Cost]

(

@ServiceMonth INT = NULL

, @ServiceYear INT = NULL