Designing Data Access - Professional SQL Server 2005 Reporting Services - page 148

Using Stored Procedures
What the best way to go about querying a data source is will depend highly on your requirements. Refer
back to the earlier discussion about filtering techniques where processing parameters (on the database
server, the client, or both) affects performance, efficiency, and the flexibility of your reporting solution.
Handling parameters on the database server will almost always be more efficient, while processing
parameters on the client will give you the flexibility of handling a wider range of records and query
options without needing to go back to the database every time you need to render the report.
Using a parameterized stored procedure is typically the most efficient means for filtering data since it
returns only the data matching your criteria. Stored procedures are compiled to native processor instruc-
tions on the database server. When any kind of query is processed, SQL Server creates an execution plan,
which defines the specific instructions that the server uses to retrieve data. In the case of a stored proce-
dure, the execution plan is prepared the first time it is executed and then it is cached on the database
server. In subsequent executions, results will be returned faster since some of the work has already been
done. Stored procedures for SQL Server can be created in three different places: the SQL Enterprise
Manager, the SQL Query Analyzer, or Visual Studio's integrated Query Builder.
In the next exercise, you create a stored procedure that will be used to create a columnar report. This is
performed using the Server Explorer to obtain a connection to the database server and then manage
objects on the server.
In Visual Studio open, you can see the Server Explorer (located on the left side of the designer by default).
If it's not there, you can enable this window using the View menu. To manage the objects in a database, you
must first define a connection to your database server. To do this, right-click the Data Connections item and
choose Add Connection from the menu. The Add Connection dialog is very similar to the one you used to
define a report data source. After saving the connection, expand it using the plus sign and then right-click
on Stored Procedures. From the pop-up menu, select Add New Stored Procedure, as shown in Figure 5-28.
Figure 5-28
This action will open a new Designer window to create a new stored procedure. The text in Figure 5-29
demonstrates the basic structure of a simple stored procedure.