游标使用

/* 创建存储过程 CREATE是创建 ALTER是修改 */
CREATE PROCEDURE proc_DayData (
@beginDate VARCHAR(50)--声明变量 *
,@endDate VARCHAR(50)
)
AS
BEGIN
CREATE TABLE #temp01 (--创建临时表
number INT
,FirstItem VARCHAR(50) --声明字段
,StationId VARCHAR(50)
);

DECLARE @stationId VARCHAR(50)
,@tempsql NVARCHAR(MAX)

DECLARE rs CURSOR--声明游标
FOR
SELECT StationId
FROM dbo.T_Mid_DayData_AQI
WHERE CreateDate >= @beginDate
AND CreateDate <= @endDate
GROUP BY StationId;
/* 打开游标 */
OPEN rs;
/* 逐个取出当前记录StationId字段的倿*/
FETCH NEXT
FROM rs
INTO @stationId

WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
BEGIN
INSERT INTO #temp01 (
number
,FirstItem
,StationId
)
SELECT COUNT(1)
,FirstItem
,MAX(StationId) AS StationId
FROM dbo.T_Mid_DayData_AQI
WHERE StationId = @stationId
AND CreateDate >= @beginDate
AND CreateDate <= @endDate
GROUP BY FirstItem

SELECT a.number
,a.FirstItem
,a.StationId
FROM #temp01 a
/*提前下一位信息*/
FETCH NEXT
FROM rs
INTO @stationId
END
/* 关闭游标 */
CLOSE rs;
/*删除游标*/
DEALLOCATE rs;

DROP TABLE #temp01;
END