ssrs Ignoring variable which is not declared

Question

This is my part of the query which i am using in the Dataset in SSRS2008

DECLARE @SERVER VARCHAR(20) =  null;

Declare @curDATEFIRST as Integer = @@DATEFIRST;
SET DATEFIRST 1;

IF @a=0 AND @b>1
  BEGIN
    DECLARE @NewStartDate DateTime = (SELECT TOP 1 StartDate FROM UserManagerDates WHERE EmployeeID=@b ORDER BY StartDate DESC);
    IF @NewStartDate>@StartDate SET @StartDate = @NewStartDate;
  END

Question: I was copying my query into dataset from ssms and when i hit refresh in dataset i only get prompted for @a and @b in define query parameters. Actually i wasn't even declaring @startdate so i was expecting @startdate variable in define query parameters tab. What changes should i make to to get prompted for @startdate along with the other two variables?

Answer 1

You are setting the @startdate parameter, it is supposed to be populated by SSRS instead of your query, that could be the reason you are not being prompted.

I think you want to use @startdate to populate @NewStartDate, if so try this:

DECLARE @SERVER VARCHAR(20) =  null;
Declare @curDATEFIRST as Integer = @@DATEFIRST;
SET DATEFIRST 1;

IF @a=0 AND @b>1
  BEGIN
    DECLARE @NewStartDate DateTime =
      (SELECT TOP 1 StartDate FROM UserManagerDates
       WHERE EmployeeID=@b ORDER BY StartDate DESC);
    IF @NewStartDate>@StartDate SET @NewStartDate = @startdate;
  END