Running 1 procedure twice using a single procedure

Question

I have a stored procedure that is run from a SSRS report, my problem now is that I need to include data from the previous year in the report. I wanted to be able to run the report once with the set of parameters an than a second time with the previous years data to include into my report to be able to compare the data line by line. What I have is creating an error. I am somewhat new at SQL Server, any help is much appreciated. This is built in SQL Server 2008

DECLARE 
   @StartDate datetime,
   @EndDate datetime,
   @iClientID int,
   @iServiceLevelID int

SET @StartDate = '1-1-13'
SET @EndDate = '12-30-13'
SET @iClientID = null

SET DATEFIRST 7

DECLARE @DATA table(iclientID int,
                    sClientCode varchar(8),
                    sClientName varchar(50),
                    sServiceLevelName varchar(50), 
                    DeailyProductionAverage float, 
                    CorrectionPercentage  float, 
                    AverageAging float, 
                    decProduction float, 
                    EffectedDate datetetime, 
                    RepID int,
                    FirstName varchar(50), 
                    LastName varchar(50), 
                    Completed float)

insert into @DATA
exec procSSRS_ClientPerformanceNew_2  @StartDate, 
                                      @EndDate, 
                                      @iClientID, 
                                      @iServiceLevelID

insert into @DATA
exec procSSRS_ClientPerformanceNew_2 dateadd(year, -1, @StartDate)
                                     dateadd(year, -1, @Enddate)
                                     @iClientID
                                     @iServiceLevelID  

Answer 1

In SQL Server, when passing an argument to a stored procedure, the argument can be either a constant or a variable. It cannot be e.g. a mathematical expression, nor can it be a function call.

So, in order to call the procedure again with the different data range, you will need to store the results of dateadd calls to variables and use those as arguments. For instance, you could re-use @StartDate and @EndDate for that if their original values are not needed later:

...

set @StartDate = dateadd(year,-1,@StartDate);
set @EndDate = dateadd(year,-1,@Enddate);

insert into @DATA
exec procSSRS_ClientPerformanceNew_2  @StartDate, 
                                      @EndDate, 
                                      @iClientID, 
                                      @iServiceLevelID
;

or you could declare two more datetime variables.

Answer 2

You are missing the commas in your parameter list when you call the procedure second time. Change your code to this to make it work:

exec procSSRS_ClientPerformanceNew_2

dateadd(year,-1,@StartDate),
dateadd(year,-1,@Enddate),
@iClientID,
@iServiceLevelID