Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jun 7, 2004

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

By William Pearson

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

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.

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM