• Marek Vavrovic

Create a Stored Procedure in SQL Server.

Updated: Sep 4

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.


Best Practices:


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):


1. spGetProducts

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)


Use AdventureWorks2012;

Go


CREATE PROC dbo.Headcounts

AS

BEGIN

SET NOCOUNT ON;


SELECT JobTitle,Status, Headcounts

FROM dbo.vwStatus

ORDER BY Headcounts desc

END

GO


exec dbo.Headcounts



B. Returning more than one result set


Use AdventureWorks2012;

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


Execute dbo.Top10SalesRep



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

(@Country nvarchar(50))

as

set nocount on

Begin

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



D. Stored Procedure with Multi value Parameter


CREATE PROC dbo.uspSales

@Region nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @pRegion nvarchar(max)

SET @pRegion = REPLACE(@Region,',',''',''')

DECLARE @SQLCode varchar(max)


SET @SQLCode='

SELECT Year([OrderDate])[OrderDate]

,[Region]

,[Representant]

,[Category]

,[Item]

,[Units]

,[UnitCost]

,[Total]

FROM [dbo].[salesCat]

where Region in ('''+@pRegion+''')'


EXECUTE (@SQLcode)


END


Exec dbo.uspSales 'Europe,America'


35 views0 comments

Recent Posts

See All