My three-part article on stored procedures (CFDJ, Vol. 3, issues 10, 12, Vol. 4, issue 2) has mushroomed into four, but since I'm sure most of you write perfect code the first time...

...this extra part will be of interest only to the small group of programmers who occasionally make mistakes when coding.

Stored procedure programming with ColdFusion has many benefits including speed, features, and data validation. However, coding errors in stored procedures can cause some of the most cryptic and sometimes misleading error messages you'll ever see. There are several issues and at least one bug to be aware of when working with stored procedures.

Decyphering Error Messages
First let's look at some of the common error messages. The most useful messages you'll receive occur when you have a coding error that prevents the stored procedure from compiling. For example, if you write a procedure with the following code:

CREATE PROCEDURE dbo.pr_test
AS
SELECT
*
FROM
testing

and you don't actually have a table named "testing", you'll receive the following error:

ODBC Error Code = S0002 (Base table not found)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'testing'.
SQL = "pr_test"

The most common error message you'll probably receive is:

ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
SQL = "pr_test"

Unfortunately, the message doesn't tell you which line is causing the problem or whether the problem lies in your ColdFusion code or in the T-SQL code. The first thing to do is to make sure that the data types in your <cfprocparam> tags match the values in your stored procedure and are in the same order. The next thing to check is that the values you're inputting into the <cfprocparam> tags match the data types you've chosen. Finally, check to make sure that any <cfprocparam> tags that are of type "out" actually return the correct variable type. (Later I'll show you how to use the Query Analyzer Debugger for this purpose.)

Another common error, especially when you're coding a procedure with lots of parameters, is:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'pr_test' expects parameter
'@BillToEmail', which was not supplied.

This error is caused by leaving out a <cfprocparam> tag, which results in the stored procedure's sending fewer arguments than the stored procedure expects.

Finally, you can receive the following error, which is caused by sending too many <cfprocparam> values to the stored procedure:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function pr_test has too many arguments specified.

You may receive other messages, but these are the most common.

A Bug
Now that you know some of the errors that can result from coding mistakes, let's look at a bug that can cause strange results in your code. First, look at the procedure in Listing 1. All listings can be accessed at www.sys-con.com/coldfusion/sourcec.cfm. Notice that there's a variable called @VendorCreditLine listed as input but that the stored procedure doesn't do anything with it. Second, assume that you forgot to set a default value for the column in the Vendor table for Vendor-CreditLine. When you run the procedure you'll receive the following error:

ODBC Error Code = 23000 (Integrity constraint violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint
'IX_users_userID_Constraint'. Cannot insert duplicate key in object 'Users'.

This would lead you to assume that somehow you had tried to insert a userid that was already in the database. However, if you search the database for the ID you'll never find it. The bug, caused by the database driver and not ColdFusion (according to Macromedia), causes the procedure to execute twice instead of erroring when it encounters an error. Since the procedure executes for a second time before rolling back the Insert code in the User table, the database thinks it has already input the information. To catch the error you need to run the procedure directly through Query Analyzer, which properly throws the correct error (a failed attempt to insert nothing into the VendorCreditLine column, which requires an integer).

A second problem, which may at first seem like a bug but is not, occurs when you use <cfprocparam> tags of type "Out" and cfsqltype "char". If you try to run a Len() function in ColdFusion without running Trim() first on a char value, you'll get a "true" result, even if the result is blank, because the char data type pads the field to its full length. If you need to see whether the char really has any content, run Len(Trim()) instead of just Len().

Problems with Numbers and Null
When you choose data types, make sure the type you choose is really the one you need. Decimal and integer data types can create hard-to-find math errors if you choose the wrong one. The integer data type can only take whole numbers (0, 1, 2, etc.). If you run the following code:

DECLARE
@MyInteger [int]
SET @MyInteger = 3
SET @MyInteger = @MyInteger/2

@MyInteger won't equal 1.5; it'll equal 2 because all fractional math is rounded in the Integer data type.

The decimal data type can contain whole numbers but also decimals. (This should be obvious.) However, decimal also has two attributes that can affect the results you get from math operations. The first is Scale; the second, Precision. Scale tells how many digits appear to the right of the decimal. Precision tells how many digits the number can contain. When you create a decimal field in a table, it defaults to a scale of 0 and a precision of 18. Make sure you change this or your decimal values will basically be treated like integers. To set a decimal variable you'll want to use the following syntax:

DECLARE
@MyDecimal [decimal](precision,scale)

Make sure you set the precision value large enough to avoid having your numbers truncated.

NULL is a strange beast that can cause many problems in your database. I strongly suggest never using it. When you're performing math operations, NULL trumps everything, resulting in NULL results. To avoid NULL values being inserted by ColdFusion into your stored procedures, always set the NULL attribute to "no":

<CFPROCPARAM TYPE="In"
CFSQLTYPE="CF_SQL_CHAR"
VALUE="#Client.UserID#"
NULL="No">

In your database, always un-check the "Allow Nulls" box in the design view () (see Figure 1).

Debugging with the Query Analyzer Debugger
The easiest and most reliable way to track down problems with a stored procedure is to use the Query Analyzer Debugger that comes with MS-SQL Server. This tool allows you to input variable values, set stop points, and view the values of all variables throughout the procedure. To use the debugger right-click a procedure in Query Analyzer and select "Debug" (see Figure 2). You'll then see the set window for the debugger (see Figure 3).

Put in a value for each of the "Input" variables and set the "Input/Output" variables to null. (The variables you set as output variables in your stored procedure will appear as "Input/Output" in the debugger.) If you just leave the output variables blank instead of setting them to null, you'll receive error messages when you run the debugger. Also, make sure the "Auto Rollback" box is checked or you'll actually make database changes when you run the debugger. When you click "Execute" you'll see the screen in Figure 4.

The top pane contains your procedure and shows breakpoints (the red dots in the left gutter). The middle pane is broken up into three sections. The first shows the values of all local variables at each step in the procedure. The second shows global variables. The third (not shown) lists all procedures being called by the debugger. If you nest procedures, this is where you'd see them. There is also a bottom pane that contains any return codes and record sets that your procedure creates.

To debug your procedure, set several breakpoints in critical areas (places where variables get set) so you can confirm that the values are what you were expecting. Using the Query Analyzer Debugger will help you code your stored procedure properly and provide some assurance that it will function in your application. But how should you handle errors that crop up in ColdFusion?

When you run the debugger, you should see "@Return_Value = 0" in the bottom pane (if the procedure ran correctly). This value can be accessed in ColdFusion using the cfstoredproc. statuscode variable. To get the value back from the database, set "return Code='yes'" in the <cfstoredproc> tag.

In your CF code you can use <cfif> to handle errors. For example:

<cfif cfstoredproc.returncode>
Put error code here.
<cfelse>
Put normal processing here.
</cfif>

Unfortunately, even if the stored procedure ran incorrectly, it's still possible that it changed things in the database. To prevent this from happening on the CF side, use transaction tags. For example:

<cftransaction>
(your stored procedure)
< cfif cfstoredproc.returncode >
<cfset Client.Message="Your stored procedure broke.">
<cftransaction action="rollback"/>
<cfelse>
<cfset Client.Message="The procedure ran correctly.">
<cftransaction action="commit"/>
</cfif>
</cftransaction>

It's also possible to code error handling within the stored procedure (see Listing 2). Notice that I check the value of "@@Error" after each statement and that I use transaction statements around each query.

Another possible way to enhance your error handling is to include an output variable that returns custom error messages from the procedure to ColdFusion (see Listing 3). This allows you to create user-friendly messages that can tell people exactly what went wrong in your application.

I hope this final article on using stored procedures with ColdFusion will help you become a more efficient coder as well as produce better applications. If you have any questions, please e-mail me.

• Figure 1
• Figure 2
• Figure 3
• Figure 4
• Source Code