Passing variables to a MS Sql Server Stored Procedure.

Document ID : KB000056001
Last Modified Date : 14/02/2018
Show Technical Document Details

Task: Creating a report that will pass a prompted value to a Stored Procedure in a MS SQL Server Database.

Overview

MS SQL Server supports Stored Procedures and CPR Builder allows you to use them in a report. If the Stored Procedure being used requires user input (aka, "Run Time Variable or RTV), you will need to properly build your report to allow that.

In this example, the MS SQL Server Pubs database will be used, with a Stored Procedure created and saved in that database.

Steps to Create the Report and the Stored Procedure:

Creating the Stored Procedure:
Open SQL Server Enterprise Manager.
Browse down until you see "Stored Procedures" listed for the database you need (in this example, Pubs).

Right-click in the Stored Procedure windows and select New Stored Procedure. This is the Stored Procedure for this example:

Create PROCEDURE dbo.Test
    @FilterDate DateTime
AS
    SET NOCOUNT ON
    SELECT * FROM SALES WHERE ord_date=@filterDate
    RETURN
GO

Name of the procedure is Test, using the Sales table using the column ord_date as the filter with the value of variable "filterDate (which requires user input).

Creating a report using the Stored Procedure:

Create a blank report:

To the blank report, add a Tabular Detail (from the Create menu), you will get the database login screen:

When you get the window for table and column selection, press Close:

You will have a blank report with a blank detail section.
Highlight the detail section and select (from the toolbar) Query > Edit.

In the Edit window, add the syntax for including the Stored Procedure:

At a minimum, you need the name of the Stored Procedure. In this example, we also need to include the input parameter.

To specify the Stored Procedure name, simply enter the name, or you could use: exec dbo.test

If you were including hard coded input, you would enclose it in []'s, for example:
stored_procedure_name [input-value].

As we are creating a RTV, you will instead press the Variable button.

As this example is using a date column in the filter, we need to select DATE from Variable Type.

You can select a default value and assign a User Prompt at this time.

After setting up your variable, press ok, and you will return to the Edit window:

Before pressing OK, check the "Do Not Modify" button.

Press OK (in the Edit window) and you will be returned to the Design View; you will see the columns specified in the Stored Procedure now appear in your detail section.

Click the Print Preview icon and you should see a screen similar to this:

Enter a valid date or use the default value (if appropriate) to get your results: