- Marek Vavrovic
Create a Stored Procedure in SQL Server.
Updated: Sep 4, 2021
A stored procedure is a group of T-Sql statements. If you have a situation, where you write the same query repeatedly, you can save that specific query as a store procedure and call it just by its name.
When naming user define store procedure, Microsoft recommends not to use sp_ as a prefix.
All system stored procedures are prefixed with sp_. This avoids any ambiguity between user
defined and system stored procedure and any conflicts, with some future system procedures.
Use the SET NOCOUNT ON statement as the first statement in the body of the procedure.
That is, place it just 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.
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.
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.
Avoid the use of SELECT *.
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.
To execute stored procedure (examples):
2. exec spGetProducts
3. execute spGetProducts
SQL SERVER – Stored Procedures Advantages
Execution Plan Retention and Reuse. SP are compiled and their execution plan is cached and used again to when the same SP is executed again. All the other advantage known of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.
To return the definition of a Transact-SQL procedure:
exec sp_helptext spGetProducts
select * from sys.sql_modules
To return the objects that are referenced by a procedure:
select * from sys.sql_expression_dependencies
A. Creating a simple Transact-SQL procedure
(With no parameters, using a view for the stored procedure)
CREATE PROC dbo.Headcounts
SET NOCOUNT ON;
SELECT JobTitle,Status, Headcounts
ORDER BY Headcounts desc
B. Returning more than one result set
CREATE PROCEDURE dbo.Top10SalesRep
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'
C. Creating a procedure with input parameters
(Procedure returns top 10 transaction orders for the selected country. If no country is selected returns a list of available countries.)
Create Proc dbo.Top10TransactionsByCountry
set nocount on
select Top(10) t.Name as Country,
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
Select Distinct(Name) as ListOfCountries from Sales.SalesTerritory
D. Stored Procedure with Multi value Parameter
CREATE PROC dbo.uspSales
SET NOCOUNT ON;
DECLARE @pRegion nvarchar(max)
SET @pRegion = REPLACE(@Region,',',''',''')
DECLARE @SQLCode varchar(max)
where Region in ('''+@pRegion+''')'
Exec dbo.uspSales 'Europe,America'