ストアドプロシージャによる一括更新

[choco]

--プロシジャーの宣言
CREATE PROCEDURE UpdateMileage as

--変数リストの宣言
DECLARE @CAR_NUMBER nvarchar(20)
DECLARE @MI2 nvarchar(20)

--カーソルの宣言
DECLARE EmpCur cursor FOR
select CAR_NUMBER,(ODOMETER-prev_odo) AS MI2
FROM DiemListTermView
WHERE ARRIVE_TIME IS NOT NULL OR ODOMETER IS NOT NULL

--カーソルを開く
OPEN EmpCur

--FETCH（行の取り出し）
FETCH NEXT FROM EmpCur INTO @CAR_NUMBER,@MI2

--LOOP
WHILE (@@fetch_status = 0)
BEGIN
--FETCH（行の取り出し）
FETCH NEXT FROM EmpCur INTO @CAR_NUMBER,@MI2
UPDATE DRIVING_CHECK SET MILEAGE = @MI2 WHERE CAR_NUMBER = @CAR_NUMBER
end

--カーソルを閉じる
CLOSE EmpCur
DEALLOCATE EmpCur

RETURN