Database Reference
In-Depth Information
Listing 13-28.
Accepting Defaults for Some Parameters
EXEC pSelQuantitiesByTitleAndDate
@ShowAll = 'False'
, @Prefix = 'PS'
When this query runs, the results include anything that has the
PS%
prefix regardless of the date the order
was placed. Figure
13-23
shows the results.
Figure 13-23.
Results for the stored procedure setting some values
After your stored procedures are created and tested, you may want to make some changes. For example;
production stored procedures commonly include a header at the beginning of the stored procedure, much like
the one that was at the beginning of our script file. Listing 13-29 places a header just before the beginning of the
stored procedure body.
Listing 13-29.
Adding a Header to Your Stored Procedure
ALTER
PROCEDURE pSelQuantitiesByTitleAndDate
(
-- 1) Define the parameter list:
-- Parameter Name, Data Type, Default Value --
@ShowAll nVarchar(4) = 'True' -- 'True|False'
, @StartDate datetime = '01/01/1990' -- 'Any valid date'
, @EndDate datetime = '01/01/2100' -- 'Any valid date'
, @Prefix nVarchar(3) = '%' -- 'Any three wildcard search characters'
)
AS
/******************************************
Title:pSelQuantitiesByTitleAndDate
Description: Which titles were sold on which dates
Developer:RRoot
