Report with stored proc running multiple stored procedures with insert statement

• michielbijnen

  Ten Centuries

  Points: 1308

  More actions

  April 15, 2014 at 3:12 pm

  #292452

  Hi,

  I wonder if this is possible in SSRS ... I use the 2012 version (Data Tools).

  I have a report that triggers a stored procedure. See below.

  Within this SP there are 2 insert statements getting data from 2 other SP's.

  When I make a dataset referring to the main SP below, SSRS does not show me any fields at all.

  Is this because it's a SP with insert statements and nested SP's?

  At the end of the SP I make a select so it should see all the fields.

  The parameters @month and @costcenter are multivalue params.

  I use a special function to convert the multivalues, selected in the report, into a string to pass it correctly to the query (comma separated).

  USE [TestDB]

  GO

  /****** Object: StoredProcedure [dbo].[_Pink_SP_StandingsRegisterDataset] Script Date: 15-4-2014 13:31:30 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[_Pink_SP_StandingsRegisterDataset]

  (

  @year INT,

  @month NVARCHAR(50),

  @costcenter NVARCHAR(500),

  @GLaccount NVARCHAR(9)

  )

  AS

  BEGIN

  /* Remove existing content*/

  DELETE FROM _Pink_TB_StandingsRegister

  /* Add records part 1 */

  INSERT INTO _Pink_TB_StandingsRegister

  EXEC _Pink_SP_StandingsRegister @year, @month, @costcenter, @GLaccount

  /* Add records part 2 */

  INSERT INTO _Pink_TB_StandingsRegister

  (

  Type,

  Row,

  Year,

  Month,

  YearDatetable,

  MonthDatetable

  )

  EXEC _Pink_SP_StandingsRegisterDatetable @year

  /* Select all records */

  SELECT*

  FROM_Pink_TB_StandingsRegister

  END

  GO