Tag: sql variables

SQL: Stored Procedures

On By Ben Larson Ph.D.In MS SQL ServerLeave a comment

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.

Example

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:

  1. MS SQL Server: Installation
  2. SQL: SELECT Statement

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

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

storedProcedure

Execute the Stored Procedure

To execute – exec “procedure name” “variable” = “value”

exec dbo.getmail @name=’Kevin’

storedProcedure1.jpg

Now change the value of @name to ‘Mary’

storedProcedure2