set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* =============================================
   Author:	Prabakar
   Description:	For a given employee SSN, check if this employee exists.
	If so, find the total number of hours worked on all projects by this emp.
   =============================================
*/
CREATE PROCEDURE [dbo].[uspEmpHours]
	-- Add the parameters for the stored procedure here
	@empSSN char(9) 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Declare local variables
	declare @currEmp char(9)
	declare @totHours Decimal (5,1)

    -- Insert statements for procedure here
	SELECT @currEmp = ssn
	From employee
	where ssn = @empSSN;
	if (@currEmp is NULL)
	begin
	  print 'The input SSN value '+ @empSSN + ' is not a valid SSN'
	  return (-1)
	end

	select @totHours = sum(hours)
	from works_on
	where essn = @empSSN;

	print 'Total works hours for ' + @empSSN + ' is ' + cast(@totHours as varchar(10))
	return(0)

END