Learning How to Create your First Stored Procedure

 
As mentioned above in the overview of this tutorial, a stored procedure is like a stored SQL code that you may require to use over and over again. In this given example we are going to create a simple stored procedure.

Description

  • Before stored procedure is created you require to know what is the desired end output, whether you are manipulating data, selecting data, inserting data, etc..
  • In this simple given example below we are just going to select all data from the “Person. Address” table that is stored in the Adventure Works database.
  • Hence the simple T-SQL code is going to get executed in the AdventureWorks database as follows that will return all rows from this table.

SELECT * FROM Person.Address
 
For creating a stored procedure perform this the code:
 
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress
AS
SELECT * FROM Person.Address
GO
 
To call the procedure for returning the contents from the table specified, the code is as follows:
 
EXEC dbo.uspGetAddress
— or
EXEC uspGetAddress
–or just simply
uspGetAddress
________________________________________

  • When a stored procedure is created you can either use keyword CREATE PROCEDURE or CREATE PROC.
  • After the name of the stored procedure you need to use the keyword “AS” and then the rest command is just the regular SQL code that normally used to execute.
  • Kindly note that you cannot use the keyword “GO” in the mentioned stored procedure. Once the SQL Server compiler finds “GO” it presumes it is the end of the batch.
  • Also, you are not allowed to change database context within the stored procedure for example using “USE dbName” the reason for this is because this would be a distinguished separate batch and a stored procedure is a collection of only single batch of statements.

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.