CODEWITHSUNDEEP
reporting-services
Outside the Box Developer
Outside the Box Developer

Reputation: 1299

SSRS getting result from SQL Stored Procedure return value

Basically, I have a stored procedure that does some checking and will return a 1 or 0.

It is not a column, field name, something like

IF (some logic) RETURN 1
....
...
RETURN 0

I don't know what to put for the field source...

I am trying to make a text box show/hidden based on the result of the SP being a 1 or 0.

=IIF(something here, false, true)

Is there an issue with trying to get the return value to be used instead of columns?

Source

No results

Upvotes: 1

Views: 1165

Answers (2)

StevenWhite
StevenWhite

Reputation: 6034

SSRS datasets expect to get column names from the query.

  1. In your stored procedure use SELECT statements to return values.

    IF (some logic) SELECT 1 as MyResult
...
SELECT 0 as MyResult

  2. In your dataset properties, reference the procedure as usual and supply any parameters to it as needed. You should see the "MyResult" field under the dataset name.

  3. In your expression you would check the result like this:

    =IIf( First(Fields!MyResult.Value, "SP_Dataset") = 1, true, false )

If you were thinking of sending different values into the stored procedure in each cell with this expression, that will not work because each dataset is only evaluated once. If that is the desired behavior, you would need to check all the rows in the initial SQL instead.

Upvotes: 0

Alan Schofield
Alan Schofield

Reputation: 21683

Just do something like this in your dataset query

DECLARE @returnvalue int
EXEC @returnvalue = SP_SomeProc

SELECT @returnValue as TheResult

The dataset will return a field called TheResult containing the value from the SP

Upvotes: 2

Related Questions