The variable name '@CreationDate' has already been declared. Variable names must be unique within a query batch......

• miércoles, 02 de mayo de 2012 16:10

   

   
  

  0

  I am having a problem inserting this query into SSRS.

  An error occurred while the query design method was being saved. The variable name '@CreationDate' has already been declared. Variable names must be unique within a query batch or stored procedure.
  
  
  

  Create Table #results(
  	ID int identity(1,1),
  	Serial varchar(20),
  	ServiceDate datetime,
  	CreationDate datetime,
  	CompletionDate datetime
  )
  Declare @ID int
  declare @Serial varchar(50)
  Declare @ServiceDate datetime
  declare @CreationDate datetime
  declare	@CompletionDate datetime
  Declare @rowID int
  insert into #results
  select 
  a1.[Asset Serial No_], 
  b1.[Service Date],
  
  NULL,
  NULL
   from 
  [Sales Invoice Line] as a1  join 
  [Sales Invoice Header] as b1 on b1.[No_] = a1.[Document No_] 
  join [Dimension Value] dv on dv.Code=b1.[Shortcut Dimension 2 Code]
  
  where a1.[Asset Serial No_]<>''
  
  and b1.[Service Date] between @startDate and @endDate
  and dv.[Name]=(Case When @name = '*All*' Then dv.[Name] Else @name End)
  
  
  set @rowID = (select MIN(ID) from #results)
  while @rowID is not NULL 
  Begin
  Select Top (1) @Serial=Serial, @ServiceDate=ServiceDate
  From #results
  where ID=@rowID
  
   select 
    top (1)
   @creationDate=[Creation Date], @CompletionDate=[Completion Date]
    from [Asset] a
     join [Job] as b on b.[Serial No_] = a.[Serial No_]
     
  		where
  		a.[Serial No_]=@Serial
  		and b.[Job Type 2] = 2 --2Repair
  
  		and [Creation Date]>=@ServiceDate
  		order by [Creation Date]
  update #results set CreationDate=@CreationDate, CompletionDate=@CompletionDate
  where ID=@rowID
  
  set @rowID=(select MIN(ID) from #results
  where ID>@rowID)
  end
  
  select * from #results
  order by [Serial]
  drop table #results

  
  

  • Responder
  • Citar