Database Reference
In-Depth Information
AND
[TitleId] LIKE @Prefix+'%'
GROUP BY
DP.PublisherName
, DT.TitleName
, DT.TitleId
, CONVERT(varchar(50), [Date], 101)
ORDER BY DP.PublisherName, [Title], [OrderDate]
END -- the body of the stored procedure --
Key points of interest are the definition of the parameter list at the beginning of a stored procedure
and before the AS keyword. This serves the same purpose as declaring variables to be used to create dynamic
queries.
One interesting aspect of stored procedure parameters is your ability to define default values. When a stored
procedure is executed and a value is not supplied for the parameter, the default will be used. If a value is given,
however, then the stored procedure will use the given value.
it is common for developers to include additional comments in their stored procedures. Listing 13-25
indicates the kind of data that is expected within the parameters. other additions that are not shown here are: a
header directly after the AS keyword that indicates information about who created the stored procedure, when it
was first created, which objects it interacts with, and a list of changes that have occurred over time. This header
information, while important, is not shown here in order to keep the code as small as possible. But we show an
example of what it should look like in just a moment.
Note
After the parameters have been defined for the stored procedure, the AS keyword is used to identify the
beginning of the stored procedure body. The body of the stored procedure contains your reporting queries and
any additional queries, and might be required to obtain the report data. Although not required, BEGIN and END are
used to identify where the body of the stored procedure starts and finishes, and it is a recommended practice to
include both.
Once the stored procedure has been created, you can execute it by using the EXECUTE keyword, which can be
written out or, as shown in Listing 13-26, can be executed using just the first four letters of the keyword, EXEC .
Listing 13-26. Using Your Stored Procedure with Default Values
EXEC pSelQuantitiesByTitleAndDate
When this code runs, the stored procedure utilizes all the default values since no additional parameter
values were given. In this case, the default value of True for the @ShowAll parameter is sufficient for the query to
return all the results unfiltered, as shown in Figure 13-21 .
 
Search WWH ::




Custom Search