USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Calculate]
    @Number1 [int],
    @Number2 [int],
    @returnMessage  VARCHAR(50) OUTPUT      --Parameter holding the return message
AS
BEGIN
    SET @returnMessage = @Number1 * @Number2
    SET @returnMessage = 'The result of this calculation is : ' + @returnMessage
End

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetEmployeeInfo]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     Person.Contact.FirstName, Person.Contact.MiddleName, Person.Contact.LastName, Person.Contact.EmailAddress,
                      HumanResources.Employee.Title
FROM         Person.Contact INNER JOIN
                      HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID AND
                      Person.Contact.ContactID = HumanResources.Employee.ContactID
End