Stored procedures are basically executable scripts you store in your database. You can then execute them as needed. Instead of discussing what they are, let’s just make one and see how it works.
We are going to create a stored procedure that returns an employee’s email address based on their first name.
I am using AdventureWorks2012 in MS SQL Server for this lesson. If you do not have either installed and would like to follow along, check out my first two SQL lessons to get up and running:
If you are already up and running, let’s consider the SQL Query below
SELECT p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,e.EmailAddress FROM [Person].[Person] as p join [Person].[EmailAddress] as e on p.BusinessEntityID = e.BusinessEntityID where p.FirstName like 'Ken'
I am using two tables, Person and EmailAddress from the schema Person
From my query, I am asking for the FirstName, MiddleName, LastName, and EmailAddress for any employee with the first name Ken.
While this works, the problem is if someone asks you to look for people named Kevin tomorrow, you would have to create this query all over again. Or, we can build a Stored Procedure
Let’s create a stored procedure. In this example, we are going to use a variable. In SQL you must declare you variables.
Variables are designated in SQL with the @ symbol in front. So our first line of code below translates to: create procedure dbo.getemail (create a stored procedure name dbo.getemail) – @name nvarchar(20) – (use a variable named @name which is a character variable limited to 20 characters)
as – (states the code following “as” will be the code executed by the stored procedure)
finally – note the where statement was altered to include the @name variable
create procedure dbo.getemail @name nvarchar(20) as SELECT p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,e.EmailAddress FROM [Person].[Person] as p join [Person].[EmailAddress] as e on p.BusinessEntityID = e.BusinessEntityID where p.FirstName = @name go
Once you execute the code above, your stored procedure can be found in your database
Execute the Stored Procedure
To execute – exec “procedure name” “variable” = “value”
exec dbo.getmail @name=’Kevin’
Now change the value of @name to ‘Mary’