Geek asks and answers

Retrieve cursor entry in SQL

I have a list of records and a cursor created to loop through each record and check for a certain condition and return record, if it satisfies my cursor, it looks like this:

DECLARE @ID int
DECLARE @FromDate datetime, @ToDate datetime
DEClare @expid as int
set @expid = 839
DECLARE IDs CURSOR FOR 
select patpid,fromdate,todate from tdp_ProviderAccomodationTariffPlan where fk_patid =    162 and fk_pacid = 36

 OPEN IDs
 FETCH NEXT FROM IDs into @ID,@FromDate,@ToDate
 WHILE @@FETCH_STATUS = 0
 BEGIN
print @ID 
print @FromDate
print @ToDate

--SELECT patpid,rate,SType FROM tdp_ProviderAccomodationTariffPlan 
--WHERE ('2012-12-27' BETWEEN @FromDate AND @ToDate) and fk_patid = 162 and fk_pacid = 36

FETCH NEXT FROM IDs into @ID,@FromDate,@ToDate  

END
CLOSE IDs
DEALLOCATE IDs

in the cursor cursor entry whose identifier is "839", please help me solve the problem.

+6
source share
4 answers

Replace the cursor with WHILE loops to get better performance as follows:

select identity(int,1,1) as id, patpid,fromdate,todate
INTO #temp1
from tdp_ProviderAccomodationTariffPlan
where fk_patid =    162 and fk_pacid = 36

declare @index int
declare @count int

select @count = count(*) from @temp1
set @index = 1

declare @patpid int
declare @fromdate datetime
declare @todate datetime

while @index <= @count
begin

  select @patid = patid,
         @fromdate = fromdate,
         @todate = todate
  from #temp1
  where id = @index

  -- do your logic here

  set @index= @index + 1
end

drop table #temp1
+6
source

Since you have a list of dates, you should declare a cursor for this list, not for tdp_ProviderAccomodationTariffPlan:

CREATE TABLE #TEMP_TABLE (PATPID INT, RATE ..., STYPE ...)
DECLARE @MY_DATE DATETIME, @FromDate DATETIME, @ToDate DATETIME
SET @FromDate = '...'
SET @ToDate = '...'
DECLARE THE_CURSOR CURSOR FOR 
select MY_DATE from YOUR_DATE_LIST 

 OPEN THE_CURSOR
 FETCH NEXT FROM THE_CURSOR into @MY_DATE
 WHILE @@FETCH_STATUS = 0
 BEGIN

 INSERT INTO #TEMP_TABLE
SELECT patpid,rate,SType FROM tdp_ProviderAccomodationTariffPlan 
WHERE (@MY_DATE BETWEEN @FromDate AND @ToDate) and fk_patid = 162 and fk_pacid = 36

FETCH NEXT FROM THE_CURSOR into @MY_DATE
END
CLOSE THE_CURSOR
DEALLOCATE THE_CURSOR   
select * from #temp_table
DROP TABLE #TEMP_TABLE

But I would recommend that you avoid using cursors. This is easier and faster to do in .NET code.

+3
ALTER PROCEDURE [dbo].[SP_UpdateEmpStatus_IfLastAttDateMoreThan50]
(
@Msg NVARCHAR(MAX)=null OUTPUT
)
AS
BEGIN 

/**Declare Cursor**/
DECLARE @TCursor CURSOR

/**Declare Cursor**/

DECLARE @EmpCode bigint=null,@maxAttDate DATE=null,@totDays INT=null

/**Creating TempDetails Table**/
CREATE TABLE #TempDetails
(EmpCode BIGINT,maxAttDate DATE,totDays int)
/**Creating TempDetails Table**/

INSERT INTO #TempDetails(EmpCode,maxAttDate,totDays)
(
SELECT  EmpCode,MAX( LastAttDate) AS maxAttDate,(DATEDIFF(DAY,MAX( LastAttDate),GETDATE())) As totDays FROM tbl_EmpdutyDays
WHERE EmpCode IN (SELECT DISTINCT EmpCode FROM tbl_Set_EmpWiseValidations WHERE Status=1)
GROUP BY EmpCode        
)

SET @TCursor =CURSOR FOR SELECT EmpCode,maxAttDate,totDays FROM #TempDetails
OPEN @TCursor 
FETCH NEXT FROM @TCursor INTO @EmpCode,@maxAttDate,@totDays
WHILE @@FETCH_STATUS=0
BEGIN
        IF(@totDays IS NOT NULL)
        BEGIN
                IF (@totDays>=50)
                BEGIN
                    UPDATE tbl_Set_EmpRestric
                    SET [Status]=0
                    WHERE EmpCode=@EmpCode

                    UPDATE tbl_Employees
                    SET [Status]=0
                    WHERE EmpCode=@EmpCode
                END

        END  

        FETCH NEXT FROM @TCursor INTO @EmpCode,@maxAttDate,@totDays
END
SET @Msg='more Than 50 days Deactived Successfully.'
DEALLOCATE @TCursor
SELECT * FROM #TempDetails
DROP TABLE #TempDetails
END
+1

@Zooz Your idea is not good enough if you deleted some record in the past, you have an index problem because the index does not have the right value for your where clause. and the table often has some deleted records.

0
source

More articles:


All Articles