在SQL中从游标获取记录

Question

我有一组记录，并创建了游标来循环遍历每个记录并检查特定条件。如果满足条件，返回该记录。我的游标如下：

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

当在循环中使用游标时，获取ID为'839'的记录，请帮我解决这个问题。

- DharaPPatel

---

你有什么问题？光标获取了错误的数据吗？你能展示一下源数据吗？ - Andrey Gordeev

我想从循环中获取id = 839的记录，看到我已经在代码中注释了select语句，我想在那里编写只返回id为839的一条记录的select语句，我该怎么做？ - DharaPPatel

还是不明白。你为什么要使用游标呢？直接使用带有WHERE子句的SELECT语句就可以了。 - Andrey Gordeev

我有一个选择语句：“select patpid,fromdate,todate from tdp_ProviderAccomodationTariffPlan where fk_patid = 162 and fk_pacid = 36”，它会返回7条记录。每个记录都返回fromdate和todate，我想检查我的日期是否在任何fromdate到todate之间，如果是，则返回该记录。 - DharaPPatel

好的，你已经编写了查询 - 你已经注释了它。为什么它对你不起作用？不要使用CURSOR，只需尝试使用你注释的查询。 - Andrey Gordeev

我有一组日期列表，想要检查它们，因此不想在我的 .NET 代码中循环，而是希望在 SQL 代码中执行。 - DharaPPatel

Answer 1

将游标替换为WHILE循环可获得更快的性能，如下所示：

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

- ZooZ

---

如果您有太多的记录，请考虑为临时表添加索引。这将极大地提升性能。 - ZooZ

Answer 2

由于您有日期列表，因此应该为该列表声明游标，而不是为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

但我建议您避免使用游标。在 .NET 代码中，这样做会更容易和更快。

- Andrey Gordeev

Answer 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 

- user11565652