Using StoredProcs with Parameters in SQL Reporting Service 2005

Introduction

SQL Reporting Services are very powerful when it comes to the options available for generating reports. We can use stored Procedure in the database to create a dataset. This article takes you through a step by step procedure on how to achieve this.

Background

For a detailed article on Defining Report Datasets, go to this link on MSDN. For a detailed article on Defining Report Datasets for a SQL Server Relational Database, go to this link. In this article, I will focus on Defining Report Datasets for a SQL Relational Database, using stored procedures with parameters.

Using the Code

1. In the designer, select New <DataSet...>:

   

2. On the Query Tab
   • For the Command Type, select Stored Procedure.
   • Enter the name of the procedure (In the Query String: text box) you want to call but don't worry about the parameters here.

   

3. On the parameters tab, create a parameter (name should include the @) for each parameter your sproc uses:

   

Now you are ready to use the dataset for SQL Server Stored Procedures with parameters.

Points of Interest

The following link helped me:

• James Geurt's Blog

History

• Version: 0.0.1.0 (I might add more screen shots and more details depending on the response)