Test_Ut_14_15_Walder

ALTER TABLE Train ADD distance INT NULL;

SELECT * FROM Train;

UPDATE Train SET distance = 2300 WHERE idTrain = '1';
UPDATE Train SET distance = 3700 WHERE idTrain = '2';
UPDATE Train SET distance = 4500 WHERE idTrain = '3';
UPDATE Train SET distance = 2900 WHERE idTrain = '4';
UPDATE Train SET distance = 1300 WHERE idTrain = '5';
UPDATE Train SET distance = 5200 WHERE idTrain = '6';


ALTER PROCEDURE SERVIS_INTERVAL
AS
BEGIN
DECLARE kurzor CURSOR FOR SELECT T.idTrain, T.name, SUM(T.distance) AS Najeto FROM Train AS T
            JOIN TrainRide AS Tr ON (T.idTrain = Tr.idTrain) GROUP BY T.idTrain, T.name;

DECLARE @v_idTrain INT, @v_name VARCHAR(20), @v_distance INT, @v_servis INT;
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_idTrain, @v_name, @v_distance;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @v_servis = ROUND(@v_distance / 1000, 0);
            PRINT 'ID:' + CAST(@v_idTrain AS VARCHAR) + ', ' + @v_name + ',
            Ujeto: ' + CAST(@v_distance AS VARCHAR) + ' km, ' + CAST(@v_servis AS VARCHAR) + '. servisni prohlidka.';
        FETCH NEXT FROM kurzor INTO @v_idTrain, @v_name, @v_distance;
        END
CLOSE kurzor;
DEALLOCATE kurzor;
END

EXECUTE SERVIS_INTERVAL;