1.  
1. Creating a simple Transact-SQL procedure (using a view with no parameters)

USE AdventureWorks;

GO

CREATE PROC dbo.Headcounts AS

BEGIN

         SET NOCOUNT ON;

         SELECT JobTitle, Status, Headcounts FROM dbo.vwStatus

         ORDER BY Headcounts desc

END

GO

1. Returning more than one result set

USE AdventureWorks;

GO

CREATE PROCEDURE dbo.Top10SalesRep AS

BEGIN

     SET NOCOUNT ON

        SELECT TOP(10) JobTitle, FullName, [Status]

            FROM vwSingleFemale

               WHERE JobTitle ='Sales Representative'

        SELECT TOP(10) LoginID, JobTitle, BirthDate, HireDate

            FROM HumanResources.Employee

            WHERE JobTitle ='Sales Representative'

END;

GO

 

1. Creating a procedure with input parameters (returns top 10 transactions for the selected country; if no country selected returns list of available countries.)

CREATE PROC dbo.Top10TransactionsByCountry (@Country nvarchar(50))

   AS

   BEGIN

set nocount on

     SELECT TOP(10) t.Name as Country, o.SubTotal

          FROM Sales.SalesOrderHeader o

     INNER JOIN Sales.SalesTerritory t

          ON o.TerritoryID = t.TerritoryID

               WHERE t.Name like @Country+'%'

     ORDER BY SubTotal desc

 

--If no country is returned, return list of countries

     IF(@@ROWCOUNT=0)

          SELECT DISTINCT(Name) as ListOfCountries

               FROM Sales.SalesTerritory