Display date in SSRS report header when t-sql resultset is empty

Question

I have a simple SSRS report that's composed of two section: the details section and the header section. The header has a title and the detail has the stored procedure resultset.

The report calls stored procedure dbo.usp_GetChanges that looks something like this:

create procedure dbo.usp_GetChanges
as 

declare @Date int

select top 1 @Date = datekey
from DatesProcessed order by DatesProcessedid desc

select RunDate, OldName, NewName from EmployeeLog
where RunDate = @Date

If the last rundate is 20150215, then the result looks like this:

20150215    Joe    John
20150215    Mary   Marie
20150215    Bob    Bobo

For the SSRS header, I use the SP's resultset to populate the title. So the title for that day's report is: Changes for 20150215

But let's say that at the moment the report's run, no changes were made, so the SP doesn't return anything. Is is possible to return @Date with that same stored procedure?

I know there are several ways to fix the issue (ie. subreports, another SP, etc); I'm looking for a solution that can be integrated into this stored procedure.

I was considering adding an OUTPUT variable to the SP (ie. @Date datetime OUTPUT) but SSRS interprets that as an input parameter. I tried hiding it in SSRS, but it asks me for the parameter value, even though it's not used for that.

Thanks.

Answer 1

In the stored procedure check if the select is returning rows. If so return the selected rows otherwise select the date.

IF NOT EXISTS(
  select RunDate, OldName, NewName from EmployeeLog
  where RunDate = @Date)
BEGIN
  select @Date RunDate
END
ELSE
  select RunDate, OldName, NewName from EmployeeLog
  where RunDate = @Date) 

Let me know if this helps