-------------------------------------------------------------------------------
------------------------ CREATE SOME Stored Procedure -------------------------
--                            and use them...
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
-- USE Projecto: Changes the database context to the Projecto database.
--
USE Projecto1
--
-------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects 
      WHERE name = 'dep_NumberOfEmployees' AND type = 'P')
   DROP PROCEDURE dep_NumberOfEmployees
GO

-- criar um procedimento com um parāmetro de entrada "depId"

CREATE PROCEDURE dep_NumberOfEmployees  @depId Int
AS 
SELECT D.DepNum, Count(*) as NEmp
FROM Departamento D, Empregado E
WHERE D.DepNum = @depId
   AND D.DepNUm = E.DepNum
Group By D.DepNum
GO

--
-- Now execute the procedure...(with depId = 1)
--
EXECUTE dep_NumberOfEmployees 1; 
GO
--
--.............................................................................
--

IF EXISTS (SELECT name FROM sysobjects 
      WHERE name = 'dep_GetNumberOfEmployees' AND type = 'P')
   DROP PROCEDURE dep_GetNumberOfEmployees
GO

CREATE PROCEDURE dep_GetNumberOfEmployees  @depId Int, @NEmp Int OUTPUT
AS 
SELECT @NEmp = Count(*)
FROM Departamento D, Empregado E
WHERE D.DepNum = @depId
   AND D.DepNUm = E.DepNum
GO

--
-- Now execute the procedure...
--
-- some local variables...
--
DECLARE @NumberOfEmployees int;
Declare @depID int

Set @depId = 1;

-- execute the procedure...
EXECUTE dep_GetNumberOfEmployees @depid, @NumberOfEmployees OUTPUT;

-- output the results...

IF @NumberOfEmployees < 5 
BEGIN   
   Select 'Too litle employees...'
END
ELSE
   SELECT 'N. Emps of dep ' + Cast(@depid as varchar(5)) + ' : ' +
          CAST(@NumberOfEmployees AS varchar(10));
GO          
          
--
--.............................................................................
--