Practice: Create a Stored Procedure from the MS Access Client
We will first create the Stored Procedure, then run
it with another EXECUTE command, as we did earlier with the System Stored
Procedure.
1.
Type the
following syntax into the SQL Pass-Through Query window:
CREATE PROCEDURE crosstab_salesvol_qtrly
AS
SELECT DATEPART(year, ord_date)
'Sales Year',
SUM(CASE DATEPART (quarter, ord_date)
WHEN 1 THEN qty ELSE 0 END) [Q1 Vol],
SUM(CASE DATEPART (quarter, ord_date)
WHEN 2 THEN qty ELSE 0 END) [Q2 Vol],
SUM(CASE DATEPART (quarter, ord_date)
WHEN 3 THEN qty ELSE 0 END) [Q3 Vol],
SUM(CASE DATEPART (quarter, ord_date)
WHEN 4 THEN qty ELSE 0 END) [Q4 Vol]
FROM sales
GROUP BY DATEPART(year, ord_date)
We will call our Stored Procedure crosstab_salesvol_qtrly
to make it easier to locate, should someone need to edit it and so forth.
The SQL
Pass-Through Query window appears, with CREATE PROCEDURE statement
in place, as shown in Illustration 29.
Illustration 29: SQL Pass-Through Query Window, with our CREATE
PROCEDURE Statement
The CREATE command for the Stored Procedure we
seek to create directly on the back-end server is now in place.
2.
Execute the statement by
clicking the Run button on the toolbar.
The command executes the CREATE PROCEDURE statement, and
Stored Procedure crosstab_salesvol_qtrly is created. Although no
feedback is received (no convenient message box, for example, announcing "Stored
Procedure created!" or words to that effect), we can see the Stored
Procedure if we go to SQL Server Enterprise Manager and expand the Pubs
database, then expand the Stored Procedures object, as shown in Illustration
30.
Illustration 30: The Stored Procedure Appears on the
Back-End Database Server
NOTE: We will not go through the navigation
of getting to the above. If you need help, see the MSSQL Server 2000
Books Online. I will mention, however, that a great way to review the
code within a Stored Procedure at the server level is simply to right-mouse
the associated object, and select Properties: the internals appear as
shown in Illustration 31.
Illustration 31: The Code behind the Stored Procedure ... Enterprise
Manager Properties Page
It is important to note, too, that permissions can be
managed at the procedure level - meaning we can control who can access and
execute the procedure in a relatively granular manner - another advantage of
using Stored Procedures from the MS Access client.
Let's save our work - we can adjust it here, too, if we
need, but we would have to drop the old procedure and replace it with another
freshly created one. Another subject for another day ...
3.
Select File
--> Save As and name the query Create_SP_crosstab_salesvol_qtrly.
4.
Click OK.
5.
Close the SQL
Pass-Through Query window.
We see
the new Stored Procedure displayed, as shown in Illustration 32.
Illustration 32: The Pass-Through Creation Query Appears in
the Query View
And so, we see that our Stored Procedure is safely
nestled on the server, awaiting our command to deliver the data we designed it
to present. In reality, of course, we would have ascertained this was the case
by running the query first, but the focus here is creating the Stored
Procedure from MS Access, and we shall see the results in our next section.
» 