p3

DROP FUNCTION dbo.ganancias
CREATE FUNCTION dbo.ganancias(@agency integer)

RETURNS integer
AS
    BEGIN
        DECLARE @ac integer
        DECLARE @am integer
        SET @am = 0
        DECLARE db_cursor CURSOR FOR
            SELECT p.amount
            FROM agency a, collection c, collection_cloths cc, cloth ct, clothunit cu, sale s, sale_cloth sc, payment p
            WHERE a.id=c.agency_id AND
                  c.id=cc.collection_id AND
                  ct.id=cc.cloth_id AND
                  ct.id=cu.cloth_id AND
                  s.id=sc.sale_id AND
                  sc.clothunit_id=cu.id AND
                  s.payment_id=p.id


        OPEN db_cursor
        FETCH NEXT FROM db_cursor INTO @ac

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @am = @am + @ac
            FETCH NEXT FROM db_cursor INTO @ac
        END

        CLOSE db_cursor
        DEALLOCATE db_cursor

   RETURN @am
   END


SELECT name, dbo.ganancias(id) FROM agency;