CODEWITHSUNDEEP
reporting-services
Surya Prakash
Surya Prakash

Reputation: 33

Is it possible to use a query with stored procedures inside if else statement as a Dataset in SSRS?

Sample query for a dataset.

if(previousParameterValue=true) exec StoredProcedure1 null, null
else exec StoredProcedure2 null, null, null, null

If I use this format, only the result of the first SP is available for the parameter and the list is getting displayed only for the first condition. I need to generate a list during runtime based on the input selected in previous parameter. Is there a way to implement this functionality in SSRS? Will making the number of output, name of the returned columns same from the two stored procedures help achieving this functionality?

Upvotes: 1

Views: 666

Answers (2)

Hannover Fist
Hannover Fist

Reputation: 10860

The Dataset Query's Expression Builder can be used to create an expression based on a parameter.

An IIF statement could check the parameter and return the text to run.

=IIF(Parameters!myParamater.Value = 1, "EXEC StoredProcedure1", "EXEC StoredProcedure2")

To make this work, the stored procedures should return the same fields. The dataset should be created using a single stored procedure without using the Expression Builder and then changed to the expression after the fields have been created and saved.

enter image description here

Upvotes: 1

Alan Schofield
Alan Schofield

Reputation: 21683

Your dataset query must always return the same structure, the same columns names with the same data types.

When the above is true it should work fine.

Personally, I create a temp table, dump the results of the SP into it and then return the temp table. That way, even if the field names are not identical or the datatypes are not quite the same (int vs smallint etc) then SSRS will only ever see the temp table structure and it will be fine.

For example.

CREATE TABLE #t(CustomerID int, CustomerName varchar(100), ValueA float, ValueB float)

IF @myParamater = 1
    BEGIN
        INSERT INTO #t(CustomerID, CustomerName, ValueA)
            EXEC myProcA 
    END
ELSE
    BEGIN
        INSERT INTO #t(CustomerID, CustomerName, ValueB)
            EXEC myProcB
    END

SELECT * FROM #t

Upvotes: 1

Related Questions