Database Reference
In-Depth Information
3.
Enter the following query, and click Execute. You should see the message
“command(s) completed successfully” in the results pane.
Create procedure sp_CountContacts_By_Title
@Title nvarchar(8),
@TitleCount int= 0 output
As
select Contact.Title, Contact.FirstName, Contact.LastName
from Person.Contact
where Contact.Title = @Title
Select @TitleCount = count(*)
from Person.Contact
where Title=@Title
return @TitleCount
You have made the stored procedure, and now you need to test it; in order to do so, enter the
following statements in the query pane, making sure you either replace the earlier statements or select
only these statements while executing.
Declare @return_value int,
@TitleCount int
Execute
@return_value=sp_CountContacts_By_Title
@Title='Mr.',
@TitleCount=@TitleCount
output
Select 'Total Title Count' =@return_value
If you look at the status bar in Figure 6-3 in the bottom-right corner, you will notice that the total
returned rows were 577; this is reflected in Figure 6-4 as Total Title Count.
