Getting started with Stored Procedures in SQL Server

By:   |   Comments (16)   |   Related: > Stored Procedures


Problem

I have been using SQL Server for some time, but all of the code that is issued against the database is embedded in the application code. I know that you can create stored procedures, but I am not exactly sure where to start or what I need to do to implement stored procedures.

Solution

Stored procedures are nothing more that a batch of T-SQL statements that are stored in the database. Instead of having to issues multiple statements from your application you can issue one command to call the stored procedure to do a batch of work instead of just one statement. In addition, since the code is stored in the database you can issue the same set of code over and over again even from different applications or a query window. To get started, the rest of this tip looks at some sample stored procedures and how you can get started and build upon them.

The below examples show you how simple it is to create stored procedures. All of these examples use the AdventureWorks database, but these examples should be pretty straightforward that you can apply these concepts to your own databases and applications.

Example 1 - simple stored procedure

This first example creates a simple stored procedure that gets the TOP 1 record from the Person.Contact table.

CREATE PROCEDURE uspGetContact 
AS 
SELECT TOP 1 ContactID, FirstName, LastName 
FROM Person.Contact

After the above has been created use the command below to execute this stored procedure.

EXEC uspGetContact

This is the results from this first query.

query results

Example 2 - stored procedure with a parameter

This next example is a modification of the first example, but this time adding a parameter that is passed into the procedure to dynamically select the records. Instead of using CREATE PROCEDURE we are using ALTER PROCEDURE to modify the procedure that we created in Example 1 instead of dropping it first and then recreating it.

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
SELECT TOP 1 ContactID, FirstName, LastName 
FROM Person.Contact 
WHERE LastName = @LastName

Below shows two different ways the stored procedure can be run. The first example just passes the parameter value we want to use and the second example also includes the parameter name along with the value. You can run the stored procedure with either one of these commands.

EXEC uspGetContact 'Alberts' 

EXEC uspGetContact @LastName='Alberts'

This is the results from this first query.

query results

Example 3 - stored procedure with a parameter and output parameter

In this example we have both an input parameter as well as an OUTPUT parameter. The output parameter will be used to pass back the ContactID that we are looking up in the stored procedure. This output parameter will then be used to select the persons ContactID, FirstName and LastName along with any address records for this person.

Again we are altering the stored procedure uspGetContact and then secondly we are running the next set of code that executes procedure uspGetContact and then based on the return value it gets it will also query for the persons name and address info. 

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output 
AS 
SELECT TOP 1 @ContactID = c.ContactID 
FROM HumanResources.Employee a  
INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
WHERE c.LastName = @LastName

After the stored procedure has been altered run the below block of code. This will execute the above stored procedure and if the ContactID has a value it will also return the person and address info.

DECLARE @ContactID INT 
SET @ContactID = 0 

EXEC uspGetContact @LastName='Smith', @ContactID=@ContactID OUTPUT 

IF @ContactID <> 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 

   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END

This is the results.

query results

Example 4 - stored procedure using the RAISERROR statement

In this example we are combining the two steps in Example 3 into one stored procedure. The first step is to get the ContactID and then the second part of the procedure will lookup the persons name and address info. We also added in code to use the RAISERROR statement to return an error if no records are found.

This is then being run twice to show what it looks like when data is found and when no data is found. The RAISERROR statement can be used to control how your application handles no data or any other error that may occur.

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
DECLARE @ContactID INT 
SELECT TOP 1 @ContactID = c.ContactID 
FROM HumanResources.Employee a  
INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
WHERE c.LastName = @LastName 

IF @@ROWCOUNT > 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 
    
   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END 
ELSE 
BEGIN 
   RAISERROR ('No record found',10,1) 
END 

EXEC uspGetContact @LastName='Walters'

This is the results.

query results
EXEC uspGetContact @LastName='Job'

This is the results when no data is found.

query results

Example 5 - stored procedure with a separate calling stored procedure

Here is another example where we have two stored procedures. The first stored procedure uspFindContact lookups the first record that has an address record and then returns the ContactID to the calling stored procedure to again display the person and address info.

CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output 
AS 
SELECT TOP 1 @ContactID = c.ContactID 
FROM HumanResources.Employee a  
INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
WHERE c.LastName = @LastName

The code below does an alter of the uspGetContact stored procedure that calls uspFindContact and returns the recordsets.

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
DECLARE @ContactID INT 
SET @ContactID = 0 

EXEC uspFindContact @LastName=@LastName, @ContactID=@ContactID OUTPUT 

IF @ContactID <> 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 

   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END 
ELSE 
BEGIN 
   RAISERROR ('No record found',10,1) 
END 

EXEC uspGetContact @LastName='Walters'

This is the results.

query results
EXEC uspGetContact @LastName='Job'

This is the results.

query results

Example 6 - stored procedure with comments

This last example takes the uspGetContact stored procedure and adds comments to the code so you can see how comments work within a stored procedure.

Comments can be made two ways

  1. using --
  2. using /* to begin the comment block and */ to end the comment block.

Other than that nothing else has changed in the stored procedure.

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
/* This is a sample stored procedure to show 
   how comments work within a stored procedure */ 

-- declare variable 
DECLARE @ContactID INT 
-- set variable value 
SET @ContactID = 0 

-- execute stored proc and return ContactID value 
EXEC uspFindContact @LastName=@LastName, @ContactID=@ContactID OUTPUT 

-- if ContactID does not equal 0 then return data else return error 
IF @ContactID <> 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 

   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END 
ELSE 
BEGIN 
   RAISERROR ('No record found',10,1) 
END

These are pretty simple examples, but hopefully this gives you an idea of how easy it is to create stored procedures for SQL Server. If you can run a SELECT statement from either a query window or from your application you can just as easily run a stored procedure as show above.

Next Steps
  • If you are not already using stored procedures hopefully this gives you some insight as to what you need to do to begin using them
  • As mentioned these are pretty simple examples, but just about anything you can do with a batch of statements can be combined into a stored procedure and then used over and over again for your applications.
  • Check out the stored procedure tutorial for more examples


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, July 7, 2022 - 1:57:09 PM - Dave Prevette Back To Top (90241)
I was disappointed in your example. I have tried to download the AW OLTP database form several sources and in not one of them is there a Person.Contact table. I cannot run your example queries without this table.

Thursday, November 10, 2016 - 1:17:28 AM - Irfan Back To Top (43733)

 Thank you so much Greg Robidoux, Your articlye is very insightful and helpful to understand the SP.

 

 

 

Thursday, September 29, 2016 - 7:40:20 AM - Imran Tamboli Back To Top (43446)

Thank you so much Greg Robidoux. your article is a very useful. 

 

Saturday, June 8, 2013 - 6:38:59 AM - Jaypee Huda Back To Top (25352)

This is very informative article. Thanks for sharing with us. Following links also helped me.

 

http://www.mindstick.com/Blog/354/Checking%20IF%20ELSE%20condition%20in%20Stored%20Procedure%20SQL%20Server

 

http://msdn.microsoft.com/en-us/library/ms182717(v=sql.90).aspx

Monday, December 24, 2012 - 1:09:05 PM - Scott Back To Top (21125)

One of the things I think I've figured out that confused me is when I write a stored procedure, I'm not writing a stored procedure; I'm writing the instructions to create or modify a stored procedure. The actual stored procedure seems to be compiled in some sort of machine language and is never visible directly. This is similar to a programming language, such as Visual Basic or C++, in that what is written is translated to machine code to execute. But it's different in that the Visual Basic functions that I write are saved as written. In Visual Basic I'm writing the function, not writing a function to create a function. (I'm sure there's a lot going on under the surface, but this is what happens on the user interface level.) 

This is a different concept for me.

Friday, December 21, 2012 - 12:42:04 AM - Scott Back To Top (21071)

Greg,

Thanks, I got it to work. I was looking for a different window in which to type the command. I would never have guessed that I should type the command right under the text of the stored procedure, in the same window. The workings of the SSMS (SQL Server Management Studio) will take some getting used to.

Scott

Thursday, December 20, 2012 - 11:00:39 AM - Greg Robidoux Back To Top (21057)

@Scott - sorry for any confusion.

Once you create a stored procedure using SSMS it is stored in the system tables.

Then from within SSMS in a query window you can execute a stored procedure as well by just typing the stored procedure name and hitting the execute button in SSMS or you can use the EXEC with the stored procedure name, it does the same thing.

If the stored procedure returns no data, does not have a SELECT, then it will do just as you said in your post.

You can also execute a stored procedure the way you mentioned using SSMS and selecting Execute from the toolbar.

Here is more info on Stored Procedures that may be helpful: http://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure/

 

Wednesday, December 19, 2012 - 10:50:57 PM - Scott Back To Top (21038)

The above sample says:

    After the above has been created use the command below to execute this stored procedure.
    EXEC uspGetContact

I've been looking for an hour for a place to enter the command.

When I execute the stored procedure by clicking on Execute in the toolbar, it just says it was successful, but I get no output.

The title of this page is Getting Started, but the simple, obvious things that an experienced user takes for granted is what often blocks new users in any environment.

Wednesday, August 15, 2012 - 11:41:43 AM - CB Back To Top (19046)

Hi.

I'm new to storedProcs

I'm trying to create a strore procedure that selects from a table Top 1 record but if a serialnumber of the max record selected is null or empty (String) I will move to the next record. If I reach the last record I return No records found.

Thanks

 

I Started with this:

CREATE PROCEDURE [dbo].[prcTopSerialNumber] @SerialNumber NVarchar(30)Output

 

AS

 

 

BEGIN

 

 

 

SelectTOP 1 @SerialNumber = SerialNumber

From

TagReadWeightAndScaleView

Where readCount in(SelectMax(A.readCount)asCountfrom TagRead A)

 

END

 

Thursday, June 14, 2012 - 1:16:44 AM - charan Back To Top (17980)

i create 4 tables with 7 columns n 6 rows each...

i want to create stored prcedure for this which contains selected 3 columns n their rows in that with primary key n foreign key relations

Thursday, September 18, 2008 - 7:54:30 AM - aprato Back To Top (1845)

 You create a temp table

 

CREATE PROCEDURE SelectCustomers
AS
BEGIN  
   SELECT * FROM Customers
END
GO

create table mytable(column_list....)

insert into mytable(column_list....)
EXEC [SelectCustomers]

 

Thursday, September 18, 2008 - 7:11:48 AM - mateia Back To Top (1844)

Hello

>In T-SQL, you can create a temp table/table variable to capture outputted rows.

Please see the example below:

USE [Northwind]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SelectCustomers
AS
BEGIN  
   SELECT * FROM Customers
END
GO


EXEC [SelectCustomers]


How can I access here in Transact-SQL, the rows returned by the  SelectCustomers stored procedure ? 


Thursday, September 18, 2008 - 6:57:47 AM - aprato Back To Top (1843)

 Your language of choice does not have a ResultSet type object?

In T-SQL, you can create a temp table/table variable to capture outputted rows.


Thursday, September 18, 2008 - 6:51:39 AM - mateia Back To Top (1842)

Hello,

Thank you for your reply. 

However, maybe I wasn't clear enough, after calling the stored procedure I  need the actual selected rows, not the number of selected rows.

I'm afraid is not possible without an OUTPUT parameter returning the selected rows in a cursor.

 

 

Thursday, September 18, 2008 - 6:42:07 AM - aprato Back To Top (1841)

There are a few ways.  Below is one approach.

 

 CREATE PROCEDURE dbo.uspGetContact
AS
SET NOCOUNT ON

SELECT ContactID, FirstName, LastName
FROM Person.Contact

return @@rowcount
go

declare @rows int
exec @rows = dbo.uspGetContact
select @rows

Thursday, September 18, 2008 - 12:08:47 AM - mateia Back To Top (1834)

Hello,

Related to this code:

CREATE PROCEDURE uspGetContact
AS
SELECT ContactID, FirstName, LastName
FROM Person.Contact
EXEC uspGetContact

After calling EXEC uspGetContact, I would like in Transact-SQL to get the actual rows (dataset) selected by the uspGetContact stored procedure. Is it possible to have something like this:

EXEC uspGetContact

SELECT * FROM DataSetReturnedByuspGetContact ??

If it's not possible to do this in Transact-SQL, then how can application programs written in any language: e.g. C++, Delphi, Visual Basic receive a recordset/dataset without any modification of the 'uspGetContact' stored procedure ?? 

 

Thank you very much

 

 














get free sql tips
agree to terms