SSRS Dataset parameters not working

Question

So below is an example of my issue without any data in. I'm having an issue where by I've created a stored proc with an IF in so that a parameter dictates which data set I create in SSRS, however when I pass the parameter in SSRS I don't get the dataset I request. My example script is as follows:

USE [InfoPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[usp_Report_SSRSTest]  
    ( 
        @ReportType         VARCHAR(255)    = NULL
    ,   @Debug              VARCHAR(1)  = NULL
)
AS

BEGIN

SET NOCOUNT ON
SET FMTONLY OFF


CREATE TABLE #data1
(
number      INT
,text1      VARCHAR(100)
,text2      VARCHAR(300)
)

CREATE TABLE #data2
(
number      INT
,text3      VARCHAR(100)
,text4      VARCHAR(300)
)



IF @ReportType = 'Y'
    BEGIN

        SELECT
                number
            ,   text1
            ,   text2

        FROM #data1

    END
ELSE

    BEGIN

        SELECT
                number
            ,   text3
            ,   text4

        FROM #data2

    END

END

So when I go into Datasets > Add Dataset, I select a "Query type" of "Stored Procedure" and select the stored proc above. I then go to parameters and in @ReportType I enter "Y" and @Debug I enter "N". I click refresh fields and then ok. But rather than passing "Y" into the stored proc it seems to be passing nothing because I get the ELSE part of the stored proc returned as available fields in my dataset. When I try with @ReportType "N" and @Debug "N" I get the available fields I require but Im assuming that this is because its passing a NULL into @ReportType.

Is there something I am missing?

When I run the proc SSMS with the same parameters as above I get the datasets I require. When I run the stored proc in Query Designer within SSRS it is also fine.

So why would my report be passing a NULL as a parameter to the stored proc even though i'm setting the parameter to a value in Dataset properties?

You mention it seems to be passing nothing, have you confirmed? Use SQL Server Profiler determine that this is correct. — Tak
thanks for the response @t_m. Yes we have run profiler and it seems that a NULL is being passed in the parameter even though we set it in the Dataset Properties. — chrissy p

Tak Tak · Accepted Answer · 2014-10-08T15:20:26

When you are in the Dataset Properties, it does not pass any value but it retrieves metadata from the stored procedure.

You need to change your stored procedure to return same columns.

Change table definition so #Data2 has the same column names as #Data1 ..

CREATE TABLE #data1
(
number      INT
,text1      VARCHAR(100)
,text2      VARCHAR(300)
)

CREATE TABLE #data2
(
number      INT
,text3      VARCHAR(100)
,text4      VARCHAR(300)
)

-- Modified to add data
INSERT INTO #data1 (number, text1, text2)
SELECT 1, 'text1', 'text2'

INSERT INTO #data2 (number, text3, text4)
SELECT 2, 'text3', 'text4'


IF @ReportType = 'Y'
BEGIN

    SELECT
            number
        ,   text1
        ,   text2
    FROM #data1
END
ELSE
BEGIN
    SELECT
            number
        ,   text3 AS text1   -- **MODIFIED**
        ,   text4 AS text2   -- **MODIFIED**
    FROM #data2

END

END

If this is not what you want then you need to give additional information why you would want to return text1, text2 from one dataset and text3, text4 from another database based on a parameter.

SSRS Dataset parameters not working

1 Answers