Creating a stored procedure for an SSRS report

Before creating the report definition, we must create a stored procedure that will be used as the data source for the report. This stored procedure is to be called Rpt_UserTaskSp.

Note: When creating stored procedures and other system elements, we recommend strongly that you follow the naming conventions already in place. For example, with this stored procedure, we identify it as a stored procedure to use in conjunction with reports using the prefix Rpt. We identify it as a stored procedure with the suffix Sp.

To create the stored procedure for this example:

  1. Open SQL Server Management Studio and connect to your database server.
  2. Select your application database and click New Query.
    SQL Server Management Studio displays a blank query window.
  3. Copy and paste this stored procedure code into the query window: 
    -- ================================================================== 
-- Stored Procedure: Rpt_UserTaskSp 
-- 
-- This is a report stored procedure used by the UserTask report demo.
-- ================================================================== 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_NULLS ON 
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rpt_UserTaskSp]') AND type IN (N'P', N'PC')) 
DROP PROCEDURE [dbo].[Rpt_UserTaskSp] 
GO 
CREATE PROCEDURE Rpt_UserTaskSp 
(
  @UsernameStarting             UsernameType       = NULL
, @UsernameEnding               UsernameType       = NULL
, @TaskNameStarting             MessageSubjectType = NULL
, @TaskNameEnding               MessageSubjectType = NULL
, @RemindDateTimeStarting       DateTimeType       = NULL
, @RemindDateTimeEnding         DateTimeType       = NULL
, @RemindDateTimeStartingOffset DateOffsetType     = NULL
, @RemindDateTimeEndingOffset   DateOffsetType     = NULL
) AS

-- Transaction management.
BEGIN TRANSACTION
SET XACT_ABORT ON

-- Set the isolation level specified for the background task 
-- or use the system default.
IF dbo.GetIsolationLevel(N'UserTaskReport') = N'COMMITTED'
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ELSE
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE
  @RptSessionID  RowPointerType
, @LowDate       DateType
, @LowCharacter  HighLowCharType
, @HighCharacter HighLowCharType;

-- A session context is created so session variables can be used.
EXEC InitSessionContextSp
  @ContextName = 'Rpt_UserTaskSp'
, @SessionID   = @RptSessionID OUTPUT;

-- Set the low and high values used for defaulting.
SET @LowDate       = dbo.LowDate();
SET @LowCharacter  = dbo.LowCharacter();
SET @HighCharacter = dbo.HighCharacter();

, @RemindDateTimeStarting       DateTimeType       = NULL
, @RemindDateTimeEnding         DateTimeType       = NULL
, @RemindDateTimeStartingOffset DateOffsetType     = NULL
, @RemindDateTimeEndingOffset   DateOffsetType     = NULL
) AS

-- Transaction management.
BEGIN TRANSACTION
SET XACT_ABORT ON

-- Set the isolation level specified for the background task 
-- or use the system default.
IF dbo.GetIsolationLevel(N'UserTaskReport') = N'COMMITTED'
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ELSE
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE
  @RptSessionID  RowPointerType
, @LowDate       DateType
, @LowCharacter  HighLowCharType
, @HighCharacter HighLowCharType;

-- A session context is created so session variables can be used.
EXEC InitSessionContextSp
  @ContextName = 'Rpt_UserTaskSp'
, @SessionID   = @RptSessionID OUTPUT;

-- Set the low and high values used for defaulting.
SET @LowDate       = dbo.LowDate();
SET @LowCharacter  = dbo.LowCharacter();
SET @HighCharacter = dbo.HighCharacter();

-- Declare variables used to create the temp table.
DECLARE
  @UserId          TokenType
, @Username        UsernameType
, @TaskName        MessageSubjectType
, @RemindDateTime  DateTimeType
, @TaskDescription NoteType
, @RowPointer      RowPointerType;

-- Create an empty temp table for the report output.
SELECT
  @UserId          AS UserId
, @Username        AS Username
, @TaskName        AS TaskName
, @RemindDateTime  AS RemindDateTime
, @TaskDescription AS TaskDescription
, @RowPointer      AS RowPointer
INTO #ReportOutput
WHERE 1=0;

-- Insert data into the temp table.
INSERT INTO #ReportOutput
SELECT
  t.UserId
, n.Username
, t.TaskName
, t.RemindDateTime
, t.TaskDescription
, t.RowPointer
FROM UserTask t
INNER JOIN UserNames n ON t.UserId = n.UserId
WHERE n.Username BETWEEN @UsernameStarting AND @UsernameEnding
AND   t.TaskName BETWEEN @TaskNameStarting AND @TaskNameEnding
AND   ISNULL(t.RemindDateTime, @LowDate) BETWEEN @RemindDateTimeStarting AND @RemindDateTimeEnding; 

-- Return the report data.
SELECT
  UserId
, Username
, TaskName
, RemindDateTime
, TaskDescription
, RowPointer
FROM #ReportOutput
ORDER BY Username, RemindDateTime, TaskName;

COMMIT TRANSACTION
EXEC CloseSessionContextSp @SessionID = @RptSessionID;
GO
  4. Click Execute and verify that the stored procedure executes successfully.
  5. Save the stored procedure with the name Rpt_UserTaskSp.sql.