Building a Report from the Ground Up - Part 1: Creating a Stored Procedure

Today I begin a multi-part series on how to use Microsoft’s Business Intelligence Development Studio (BIDS) and SQL Server Managements Studio to create a dynamic report from the ground up. The purpose of this series of articles is to help developers who probably have basic knowledge of T-SQL and SQL Server, but aren’t sure where to start when it comes to creating reports. Over the next few weeks I’ll cover topics such as creating shared data sources, using multi-value parameters, using expressions, adding drill-through functionality, and finally deploying your reports to your report server.

Before we get into the nuts and bolts of creating a stored procedure, let’s cover a few reasons why you might want to use a stored procedure as opposed to coding the T-SQL statements inside your report, SSIS package, application, or wherever you may use a T-SQL statement.

1)      Troubleshooting: This is one of the biggest reasons I use stored procedures. When you run into a problem, it’s much easier to trouble shoot a single stored procedure than to have to dig through hundreds or thousands of lines of source code in your client application to try and expose the problem.

2)      Maintenance: Imagine that a minor change needs to be made to the database and you haven’t used stored procedures for your SQL queries. In order to reflect the change on the front-end, other changes are going to need to be made to your application’s source code because all of your SQL statements were written inside your app. If you had used stored procedures for your SQL statements, you probably wouldn’t efven have to look at your application’s source code (as long as the parameters and result columns remained the same). The app developers would never even have to know a change had been made!

3)      Security: In a stored procedure, it’s much easier to grant the appropriate permissions to users who need to access data rather than trying to code the permissions in your client application. Using stored procedures is a really good way to make sure that only authorized users have access to your data.

4)      Reusability: A single stored procedure can be called multiple times by multiple applications. Instead of having to code your SQL statement (which could actually be multiple SQL statements) each time you need to access the data, just call the stored procedure and save yourself lots of time.

5)      Performance: Once a stored procedure has been called, it stays cached in your SQL Server’s procedure cache so any subsequent executions of that stored procedure are faster, which also reduces server overhead. Calling a stored procedure also reduces network traffic. Instead of sending an entire SQL statement across the network, which could be over 100 lines of code, use a stored procedure and send only a couple lines of code.

 Now that we have discussed why you should use a stored procedure to access your data, let’s get started creating one.

1.        Open SQL Server Management Studio and connect to the database you wish to query.

2.       Expand the “Databases” node on the left in the Object Explorer window. For this example, I’ll be querying the AdventureWorks database, so I’ll expand the “AdventureWorks” node and then expand the “Programmability” node.

3.       Next, right-click on the “Stored Procedures” folder and select “New Stored Procedure…”

4.       This opens up the Create Procedure template, which provides the basic template for creating your first stored procedure. Lines with a double dash (--) out front are commented out and will not be executed as part of your SQL query.

5.       Let’s take a look at the Create Procedure template in sections. We’ll first begin with the Author and Description Section.

-- =============================================

-- Author:           <Author,,Name>

-- Create date:      <Create Date,,>

-- Description:      <Description,,>

-- =============================================

This is where you can you can provide your name, creation date, and a description of the purpose of the stored procedure. Highlight <Author,,Name> and type your name. In the place of <Create Date,,>, type today’s date, and in place of <Description,,>, provide a brief description of the stored procedure. This is important so that anyone viewing or editing the stored procedure later on down the road will have a little insight into the stored procedure’s purpose. Make sure you include these details, especially if the stored procedure is especially complicated. Later, if you come back to make a change to the stored procedure, this is where you could document those changes by adding a line like this, for example:

-- UpdateDate/Change/Author: 1/1/09 Added select statement –Dustin

6.       Next, its time to name your stored procedure. You’ll add your stored procedure on the following line:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

Replace

<Procedure_Name, sysname, ProcedureName>

with the name of your stored procedure. Make the name meaningful so someone can identify the purpose of the stored procedure without having to open it. For this example, I’m creating a stored procedure that returns a list of the products from the Adventure Works database so I’m naming this stored procedure rpt_Get_ProductList.

CREATE PROCEDURE rpt_Get_ProductList 

7.       Now its time to declare any parameters you need to use in your SQL query. A parameter allows a user to execute a stored procedure and only return certain desired results set. This is where you will add the parameter(s).

-- Add the parameters for the stored procedure here

   <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

   <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

Replace

<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>

with your parameter name and the parameter’s datatype. Here I have added a parameter called @ProductID with a valuetype of integer. The @ sign clarifies @ProductID as a variable.

-- Add the parameters for the stored procedure here

   @ProductID int

Since I’m only using a single parameter, I simply deleted

<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

If I was using more than one parameter, I would separate the multiple paramters with a comma, declaring each parameters data type after naming it.

Example:

@ProductID int,

   @ProductName varchar(50)

8.       Finally, its time to insert our SQL statement into the stored procedure. Add your SQL statement here :

-- Insert statements for procedure here

   SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

Simply highlight and delete

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

and key in your SQL statement.  Here’s what mine looks like:

-- Insert statements for procedure here

   SELECT     ProductID, Name

   FROM       Production.Product

   WHERE      @ProductID = ProductID

   ORDER BY   Name

Your SQL statement could contain Insert statements, Update statements, Delete statements, or a whole slew of other SQL commands. That’s the beauty of a stored procedure.

9.       Our stored procedure is just about ready to be added to the database. Before we add it to the database though, click the Parse Query button  in the menu bar at the to check the syntax of our SQL statement. If the query executed successfully, you should see a Command(s) completed successfully message in the Results window.

If you received an error, check the syntax of your SQL statement for errors. If you did not receive an error, press the Execute button  to Execute the Create Procedure command and add your stored procedure to your database. If the stored procedure was successfully added to the database, you will see the Command(s) completed successfully in the Results window again. To check the database for your stored procedure, expand the Stored Procedure node in the Object Explorer window. Right-click the Stored-Procedure node and select Refresh to refresh the list of stored procedures and you should see your newly created stored procedure, ready to be called and executed outside of SQL Server Management Studio!

FYI: To modify an existing stored procedure, simply right-click the stored procedure in the Object Explorer and select Modify. Make any necessary changes and execute the stored procedure to commit those changes.

I hope you found this article helpful. Next week I’ll be covering how to create your first report using BIDS and how to add a shared data source to access the database, so stay tuned. If you have any questions or anything to add, please leave a comment or email me at dryan@pragmaticworks.com.

Heres my completed Create Procedure script:

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Dustin Ryan

-- Create date: November 9, 2009

-- Description:   This stored procedure returns a list of the products

-- from the AdventureWorks.Product table in alphabetical order.

-- UpdateDate/Change/Author:

-- =============================================

CREATE PROCEDURE rpt_Get_ProductList

      -- Add the parameters for the stored procedure here

      @ProductID int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

      SELECT     ProductID, Name

      FROM       Production.Product

      WHERE      @ProductID = ProductID

      ORDER BY   Name 

END

GO