在SQL Server 2005中为临时表创建游标

debugcn 发表于 Dev

皮肤

我有一些数据插入到临时表中，并且我想通过使用LOOP显示COUNTCURSOR

这是临时表：-

SELECT * 
INTO #temp
 FROM (
select distinct a.CUser_id, b.User_Id, b.first_name + ' ' + b.last_name NAME
from inward_doc_tracking_trl a, user_mst b
where a.CUser_id = b.mkey
and CStatus_flag = 1
and NStatus_flag = 4
) AS x
Select * from #temp order by NAME

[！[TEMP TABLE数据] [1]] [1]

我刚接触光标，请帮忙

我在用 sql server 2005

PKirby

根据我们在聊天中的讨论：

DECLARE @Count INT


SELECT * 
            INTO #temp
             FROM (
            select distinct a.CUser_id, b.User_Id, b.first_name + ' ' + b.last_name AS NAME, 0 AS RECEIVED 
            from inward_doc_tracking_trl a, user_mst b
            where a.CUser_id = b.mkey
            and CStatus_flag = 1
            and NStatus_flag = 4
            ) AS x


DECLARE Cur_1 CURSOR
    FOR SELECT CUser_id, User_Id FROM #temp


OPEN Cur_1
DECLARE @CUser_id INT
DECLARE @User_Id INT
FETCH NEXT FROM Cur_1 
INTO @CUser_id, @User_Id

    WHILE (@@FETCH_STATUS = 0)
        BEGIN

        SELECT @Count = COUNT(*) FROM inward_doc_tracking_trl
        WHERE CUser_id = @CUser_id 
        AND NStatus_flag = 4 
        AND CStatus_flag = 1 
        AND a.U_datetime BETWEEN '01/04/2016' AND GETDATE()

        UPDATE #temp
        SET RECEIVED = @Count
        WHERE CUser_id = @CUser_id
        AND User_Id = @User_Id

    FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
        END
CLOSE Cur_1
DEALLOCATE Cur_1

SELECT * FROM #temp
DROP TABLE #temp

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。