Search code examples
datetimereporting-servicesparametersintervalstemp-tables

SSRS Create temporary datetime table updated with 15 minute interval - parameter not showing

I'm creating a temporary datetime table with 15 minutes increment with the following code in SSRS:

--declare @Id varchar(20), @startdate datetime,@enddate datetime
--set @ID = 'J00000041'
--set @startdate = '20210601'
--set @enddate = '20210630';

create table #tempcalendar ([dispdate] [datetime], intervaldate [datetime],intervaltime [datetime])


    while @StartDate <= @EndDate+1
        begin
            insert into #tempcalendar values(@StartDate, convert(varchar,@StartDate,110),   convert(varchar(5),@StartDate,108) )
            set (@StartDate) = dateadd(minute,15,@StartDate)
            
        end

One of the parameters doesn't appear in the Define Query Parameters dialogue box when I try to run it in SSRS and I get the message 'Must declare the scalar variable' error. I've pinpointed the issue where in the code:

set @StartDate = dateadd(minute,15,@StartDate)

I was able to perform this code in SSMS (which is where I created it initially) however I can't seem to find any information where I might have gone awry in this in SSRS. I've also tried putting parenthesis in @StartDate to show set (@StartDate) = dateadd(minute,15,@StartDate), but that didn't work.

Thank you for your help

Solution

  • I have seen this before when setting a variable in t-sql that is passed in from SSRS.

    I think the problem is the SSRS mis-interprets the query.

    I worked around the problem by setting another variable to the passed in value. e.g.

    DECLARE @StartDate2 datetime
SET @StartDate2 = @StartDate

    .. then use @StartDate2 in the remainder of the query.

    Also check that every instance of your variable/parameter names are exactly the same, they are case sensitive. If you pass in @X but reference @x SSRS will think this is a new parameter.