fetch record from cursor in SQL

Question

i have list of records and have created cursor to loop through each record and check certain condition and return record if it satisfies my cursor is as follows :

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 loop cursor fetch record whose id is '839' , Please help me solve the problem.

What is your problem? The cursor is fetching wrong data? Can you show the source data? — Andrey Gordeev
– Andrey Gordeev, Commented Mar 4, 2013 at 5:58
i want to fetch record which is having id = 839 from loop , see i have commented select statement in the code that where i want to write select statement which returns only one record where id is 839 how do i do that — DharaPPatel
– DharaPPatel, Commented Mar 4, 2013 at 6:02
Still didn't get it. Why do you use cursor then? Just use SELECT statement with WHERE clause — Andrey Gordeev
– Andrey Gordeev, Commented Mar 4, 2013 at 6:09
i have select statement "select patpid,fromdate,todate from tdp_ProviderAccomodationTariffPlan where fk_patid = 162 and fk_pacid = 36" which returns me 7 records. every record returns fromdate and todate , i want to check my date that does it lies between any fromdate to todate then it will return dat record — DharaPPatel
– DharaPPatel, Commented Mar 4, 2013 at 6:19
Ok, you already have your query written - you have commented it. Why it is not working for you? Do not use CURSOR, just try to use your commented query — Andrey Gordeev
– Andrey Gordeev, Commented Mar 4, 2013 at 6:22

ZooZ · Accepted Answer · 2013-03-05 05:51:26Z

7

Replace your cursor with WHILE loops to gain faster 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

answered Mar 5, 2013 at 5:51

ZooZ

9911 gold badge18 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ZooZ Over a year ago

If you have too many records, consider adding an index to the temporary table. This will add huge performance enhancement.

Andrey Gordeev · Accepted Answer · 2013-03-04 06:57:43Z

Since you have list of dates, you should declare the cursor for that 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 you to avoid of using cursors. It's easier and faster to do that in .NET code

score 0 · Accepted Answer · 2019-05-28 10:04:38Z

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

Collectives™ on Stack Overflow

fetch record from cursor in SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related