-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Prabakar
-- Description:	Get the salary of an employee
-- =============================================
CREATE PROCEDURE uspGetEmpSalary 
	-- Add the parameters for the stored procedure here
	@LastName varchar(15) -- Last name 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Declare the local variable here
	DECLARE @EmpIncome decimal(10,2)

    -- Insert statements for procedure here
	-- SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    Select @EmpIncome = salary
    from employee
    where lname = @LastName;
    
    -- check if a department exists with the given department name
    if ( @EmpIncome is null )
    begin
      print 'No employee exists with the last name ' + @LastName
      return (-1)
    end
    print 'Salary of the employee with the last name ' + @LastName +
         ' is ' + cast(@EmpIncome as varchar(10))
    return (0)
END
GO