Writing Stored Procedures for Microsoft SQL Server

Passing Parameters into Stored Procedures

With any development language, it is always important to be able to pass data into functions so that it can be processed or acted upon in some way. SQL Server is no different. As I discussed in Chapter 1, "SQL Server and Stored Procedure Background," stored procedures can be considered application functions and you will need to pass data into them as well. First, take a look at the stored procedure created in Listing 6.1.

Code Listing 6.1. Stored Procedure Without Parameters

CREATE PROCEDURE usp_c6_1 AS SELECT au_lname + ', '+ au_fname, phone FROM authors GO EXEC usp_c6_1

This stored procedure returns all the names and phone numbers of all entries in the authors table. Although there are some uses ...