Stored Procedure

When naming a user-defined stored procedure, remember that the stored procedures created by the system are already using the prefix "sp_" for identity. So, use anything else and you'll avoid any confusion on your versus their stored procedures and any new ones added to the system.

Before we get too deep into the topic, here are some best practices you should consider using (see the code sample below):

1. Use the SET NOCOUNT ON clause as the first statement in the body of the procedure (after BEGIN and after the AS keyword). This turns off messages that SQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed.
2. Use schema names when creating or referencing database objects in the procedure. It takes less processing time for the Database Engine to resolve object names if it does not have to search multiple schemas.
3. Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.
4. Avoid the use of SELECT * (SELECT ALL).
5. Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible.

Here is an example of a stored procedure that incorporates these items.

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

NOTE: Remember to change the year in the AdventureWorks database name.

To execute a stored procedure

There are three ways to execute a stored procedure:

1. spGetProducts
2. exec spGetProducts
3. execute spGetProducts

ASSIGNMENT

Enter the code for each of the following examples into your IDE and run them against the AdventureWorks database. Screen captures your results and pastes them into a text document that you upload to this assignment.

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

END;