jeudi 26 mai 2016

Stored procedure not working in sql server 2005

I want to run this SP but it is not working and giving error as

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

ALTER PROCEDURE GET_RECORDS_FORDATE

@From_date Datetime,
@To_date Datetime

 AS
BEGIN

DECLARE @Total INT
DECLARE @Inward INT
DECLARE @First_Level_Transfer INT
DECLARE @Data_Entry_Transfer INT
DECLARE @2nd_Level_Transfer INT
DECLARE @Outward_Transfer INT
DECLARE @Closed INT
DECLARE @Communication_Transfer INT

SELECT * 
INTO #temp 
 FROM ( 
    select  User_Id, Mkey, 0 Total, 0 Inward, 0 First_Level_Transfer, 0   Data_Entry_Transfer, 
            0 Second_Level_Transfer, 0 Outward_Transfer,
            0 Closed, 0 Communication_Transfer
    from inward_doc_tracking_hdr
 ) AS x 

 DECLARE Cur_1 CURSOR 
   FOR SELECT  User_Id FROM #temp 

    OPEN Cur_2 
  DECLARE @User_Id INT 
 FETCH NEXT FROM Cur_2 
  INTO  @User_Id 

WHILE (@@FETCH_STATUS = 0) 
  BEGIN 

   /***** Total *******/ 

   select 
   @Total = count(*), 'Total' stage, doc_date Till_date from  
inward_doc_tracking_hdr    
where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)  
 group by doc_date
/***** Inward *******/ 
  select 
 @Inward = count(*), 'Inward' stage, doc_date Till_date from  
inward_doc_tracking_hdr    
 where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)  
 and status_flag in ('6')
    group by doc_date

 /***** 1st Level Transfer *******/ 

select @First_Level_Transfer = count(*), '1st Level Transfer' stage,    doc_date Till_date from  
inward_doc_tracking_hdr    
where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)  
 and status_flag in ('4','26','24')
group by doc_date

/***** Data Entry Transfer *******/ 

select @Data_Entry_Transfer = count(*), 'Data Entry Transfer' stage,    doc_date Till_date from  
inward_doc_tracking_hdr    
where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)  
and status_flag in ('15','20')
group by doc_date
 /***** 2nd Level Transfer *******/ 

  select @2nd_Level_Transfer = count(*), '2nd Level Transfer' stage,   doc_date Till_date from  
inward_doc_tracking_hdr    
where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)   
and status_flag in ('17','21')
group by doc_date

/***** Outward Transfer *******/ 

 select @Outward_Transfer = count(*), 'Outward Transfer' stage, doc_date Till_date from  
inward_doc_tracking_hdr    
where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)  
 and status_flag='18'
 group by doc_date

 /***** Close *******/ 

  select @Closed = count(*), 'Close' stage, doc_date Till_date from  
inward_doc_tracking_hdr    
where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)   
and status_flag='5'
 group by doc_date
 /***** Communication Transfer *******/ 

  select @Communication_Transfer = count(*), 'Communication Transfer' stage,   doc_date Till_date from  
  inward_doc_tracking_hdr    
where 
doc_date between convert(datetime, @From_date,103) 
and convert(datetime, @To_date, 103)  
 and status_flag='16'
 group by doc_date


    Update #temp
     SET 
   Total = @Total, 
     Inward = @Inward,
   First_Level_Transfer = @First_Level_Transfer,
   Data_Entry_Transfer = @Data_Entry_Transfer,
   Second_Level_Transfer = @2nd_Level_Transfer,
   Outward_Transfer = @Outward_Transfer,
   Closed = @Closed,
   Communication_Transfer = @Communication_Transfer

  FETCH NEXT FROM Cur_2 INTO @User_Id 
 END 
 CLOSE Cur_2 
  DEALLOCATE Cur_2 


   SELECT * FROM #temp 
 END

I dont know what's the error.

I am using sql-server-2005

Aucun commentaire:

Enregistrer un commentaire