Database Reference
In-Depth Information
Note The prefix sp_ is a T-SQL convention that usually indicates that the stored procedure is coded in SQL. The
prefix xp_ (for “extended procedure”) is used to indicate that the stored procedure isn't written in SQL. (However,
not all sp_ stored procedures provided by SQL Server are written in SQL.) By the way, hundreds of sp_ (and other)
stored procedures are provided by SQL Server 2012 to perform a wide variety of common tasks.
Try It: Creating a Stored Procedure with an Input Parameter
Let's create a stored procedure that produces a list of contacts for a given title of any person. We'll pass
the title to the stored procedure for use in a query.
1. Enter the following query, and click Execute. You should see the message
“command(s) completed successfully” in the results pane.
Create procedure sp_Contact_By_Title
@Title nvarchar(8)
As
select Contact.Title, Contact.FirstName, Contact.LastName
from Person.Contact
where Contact.Title = @Title
2. To execute the stored procedure, enter the command along with the value for
the parameter, select the following statement, and then click Execute. You
should see the results shown in Figure 6-3.
execute sp_Contact_By_Title 'Mr.'
 
Search WWH ::




Custom Search