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