MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 8

June 7, 2004

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.

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.

The CREATE command for the Stored Procedure we seek to create directly on the back-end server is now in place.

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.

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.

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 ...

We see the new Stored Procedure displayed, as shown in Illustration 32.

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.