tsql_1

--CREATE PROCEDURE VypisNejhorsiDistrib(@p_storeID int)
ALTER PROCEDURE VypisNejhorsiDistrib(@p_storeID int)
AS
    DECLARE @v_distributorID INT;
    DECLARE @v_distributorName VARCHAR(30);
    DECLARE @v_corruptCount INT;
    DECLARE @v_now DATE;
BEGIN
    SELECT @v_now=GETDATE();

    DECLARE ccc CURSOR FOR
        Select TOP 5 di.distributorId,di.name, SUM(dl.corruptCount) as cc FROM Distributor di JOIN Delivery dl ON dl.distributorId=di.distributorId
            WHERE (dl.storeID=@p_storeID AND DATEDIFF(day, dl.date, @v_now)<=6000)
            GROUP BY di.distributorId,di.name
            ORDER BY cc ASC;

    OPEN ccc;
    FETCH NEXT FROM ccc INTO @v_distributorID, @v_distributorName, @v_corruptCount;
    WHILE(@@FETCH_STATUS=0)BEGIN
        PRINT 'DistID: ' + CAST(@v_distributorID AS VARCHAR) + ' DistName: ' + @v_distributorName + ' PocetVadnych: ' + CAST(@v_corruptCount AS VARCHAR);

        FETCH NEXT FROM ccc INTO @v_distributorID, @v_distributorName, @v_corruptCount;
    END;
    CLOSE ccc;
    DEALLOCATE ccc;
END;

BEGIN
    EXECUTE VypisNejhorsiDistrib 1;
END;


ALTER PROCEDURE MesicniUzaverka(@p_sum int OUTPUT, @p_employeeId int)
AS
    DECLARE @v_now DATE;
BEGIN
    SELECT @v_now=GETDATE();
    SET @p_sum = 0;

    SELECT @p_sum=ii.cost FROM Invoice ii WHERE ii.employeeId=@p_employeeId AND MONTH(@v_now)=MONTH(ii.date) AND YEAR(@v_now)=YEAR(ii.date);
END;


BEGIN
    DECLARE @v_outputvar INT;
    SET @v_outputvar = 5;

    EXECUTE MesicniUzaverka @v_outputvar output, 1;

    PRINT 'Suma a tento mesic: ' + CAST(@v_outputvar as VARCHAR);
END;