The Power of Stored Procedures

Stored procedures are more powerful than they look

Stored procedures for database access are more powerful than they look. At first glance, a stored procedure seems just to encapsulate a SQL statement into a function similar to a Visual Basic (VB) function or procedure. The stored procedure becomes an object in the database and works like a function, but its value goes far beyond these features.

Saving a stored procedure to the database actually precompiles it. This precompilation takes some of the processing out of executing the stored procedure at runtime, so it executes much faster than standard SQL (also known as dynamic SQL). Also, you can use Transact-SQL (T-SQL) to embed conditional logic in a stored procedure. Again, these features are just the tip of the iceberg. It pays to study and learn about using stored procedures and T-SQL in your applications. In this article, I focus on using stored procedures with the new Data Environment in VB 6.0.

In "Jump Start Your Database Project" (Premiere issue), I used the CustomerInfo application to demon- strate how to use the Data Environment. You can download the original application (CustomerInfo Original.zip) on the SQL Server Magazine Web site.

For this article, I modified this application to use stored procedures instead of SQL text in the application (CustomerInfoSP.zip). To modify the application, you need the original code just mentioned, the Northwind sample database running on SQL Server, and permission to create and use stored procedures in that database.

Now, let's walk through creating and using stored procedures so you can see how to use them with your applications. Also, I'll show you how to create stored procedures and use them with the Data Environment.

To start, open the CustomerInfo.vbp file in the VB IDE. Change the data command cmdCustomer so that it uses a stored procedure instead of just SQL. You can create the stored procedure from SQL in the data command. First, open the Data Environment (envNorthwind) containing the data commands. Then, open the properties for the data command cmdCustomer, and click the SQL Builder button to open Query Designer. The original SQL statement is

SELECT CustomerID, CompanyName, ContactName, ContactTitle,Address, City, Region, PostalCode,Country, Phone, FaxFROM Customers

To turn this statement into a stored procedure, you need to modify its syntax. In Query Designer, you can do so easily because you can use the SQL statement above. If you are creating a new stored procedure from scratch, you can still create and test the SQL statement in Query Designer, then generate the stored procedure after you confirm that it works correctly. Then, to change the previous SQL statement into a stored procedure, change the SQL statement to:

CREATE proc RetrieveCustomers AS SELECT CustomerID, CompanyName, ContactName, ContactTitle,Address, City, Region, PostalCode,Country, Phone, FaxFROM Customers

Now, click anywhere in Query Designer outside the SQL window, and Query Designer will display the dialog box you see in Screen 1, page 66. This message states that you have entered SQL text that the Query Designer does not recognize and therefore cannot display in its other windows (Diagram, Grid, and Results). Answering Yes to this prompt causes reformatting of the text and lets you manipulate it in the SQL window. Answering No will remove your last changes. Before I modified the SQL statement to create the stored procedure, I executed the original SQL code to test it.

Next, right-click any pane except the SQL pane to execute the query to create the new stored procedure. Then select Run from the menu. This selection will send the SQL statement to SQL Server and execute it. After execution completes, Query Designer will display a dialog box confirming the execution of the stored procedure. If errors occur, the Query Designer will display a dialog box identifying the appropriate error.

After you have executed the code to create the stored procedure, the new stored procedure will show up in Data View in the Stored Procedures folder under the data connection you are using. To immediately see the new stored procedure, right-click either the data connection name or the Stored Procedures folder in Data View and select the Refresh command. Screen 2 shows the new stored procedure in Data View with its members displayed.

You can edit the stored procedure directly from Data View by right-clicking it and selecting Design from the shortcut menu. This selection opens the stored procedure in the stored procedure text editor. You can change the SQL code, then save the stored procedure to update the stored procedure in the database. To save a stored procedure to the database, right-click the editor and select Save to Database from the menu or press Ctrl+S.

You can edit a stored procedure with the Query Designer. To do so, open the stored procedure in the editor, copy the stored procedure's SQL code to the clipboard, then paste it into a new Query Designer query. Make your changes, then add the code to recreate the stored procedure, and execute it again. The process takes only seconds for a simple stored procedure. For instance, you can open your new stored procedure in the stored procedure editor and copy this portion to the clipboard:

SELECT CustomerID, CompanyName, ContactName, ContactTitle,Address, City, Region, PostalCode,Country, Phone, FaxFROM Customers

Next, you can paste this statement into Query Designer and modify it as before. If the stored procedure is more complex, you can copy the SQL statement from Query Designer and paste it into the stored procedure editor.

The data command's property page lets you set or change a data command to use a stored procedure. To change the data command cmdCustomer, open the properties for the data command, then select Stored Procedure from the database object list. Next, select the stored procedure RetrieveCustomers from the list of stored procedures. After you have made your changes, close the properties. Screen 3 shows the data command properties after you change the setting to use the stored procedure instead of the SQL statement.

Parameters, Anyone?

Using parameterized stored procedures takes a bit more work. Let's look at the data command cmdCustomerByID. This data command takes a customer ID as a parameter and uses it in the WHERE clause to retrieve a single customer record. The current SQL statement is

SELECT CustomerID,CompanyName, ContactName, ContactTitle,Address, City, Region, PostalCode,Country, Phone, FaxFROM Customers WHERE (CustomerID = ?)

The question mark is the Data Environment shorthand for the CustomerID parameter you want to pass into the query. To create the stored procedure, you need to change the SQL statement to work in SQL Server instead of the Data Environment because the stored procedure executes entirely inside SQL Server. To make a parameterized query work requires defining the incoming parameter for the customer ID. At the same time you add the code to create the stored procedure, make this change:

Create Procedure CustomerByID @CustomerID char(5) AS

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, FaxFROM CustomersWHERE CustomerID = @CustomerID

The syntax and detail here are important. The @CustomerID variable in the first line defines the parameter. SQL Server uses the @ symbol to define variables. In addition, you must modify the WHERE clause to correctly use the defined parameter instead of the Data Environment parameter, which is a question mark.

Also, you must use the correct datatypes for all parameters and variables. For instance, if you set the datatype for the parameter incorrectly, the procedure might not generate an error when it executes and will not return any data because the WHERE clause does not match the incoming parameter with data in the table.

Finding this type of problem takes trial and error. You must pay special attention to this problem when you check datatypes in the database. For instance, the CustomerID column in the database has a datatype of nchar and is 5 characters long. nchar is a Unicode character datatype. If you simply define the parameter with a datatype of nchar, the stored procedure will not work. Defining the parameter as char(5) works because it matches the length of the CustomerID column in the database and matches the type of data (character) that you are passing in. SQL Server does an implicit conversion from char to nchar when the command executes. Also, you can use the Cast statement to force an explicit conversion of one datatype to another. For more information, search for nchar or Cast in the SQL Server Books Online (BOL).

After you have completed the changes to the SQL statement, execute it, as you did with the first stored procedure example to save the changes in the database. You can test the new stored procedure without leaving Query Designer. Simply enter the command you want to execute in the SQL pane and execute it. You can execute a stored procedure with the execute statement:

EXECUTE customerbyid 'ANTON'

You can see the results of the stored procedure in the Results pane of Query Designer.

Next, change the settings for the data command to use the stored procedure just as you did for the first one. Open the properties for the data command cmdCustomerByID, and select Stored Procedure from the database object list. Then, select the stored procedure dbo.CustomerByID from the list of stored procedures. At this point, the data command is using the stored procedure. You can access the settings for the stored procedure's parameters by clicking the Parameters tab, but you probably will not need to change them. Screen 4 shows the settings for the CustomerID property. After you have made your changes, close the properties.

Now the application is ready to execute. You will see an immediate change for the better in the application's performance.

Add Zip to Your App

This article touches on the basics of using stored procedures. You can also create, update, and insert stored procedures. These types of procedures can really add zip to your application, especially if you have several users performing inserts or updates at the same time.