First of all, it is my preference to explicitly check for an existing
duplicate value prior to insertion instead of allowing SQL server to
throw the error. The column constraint is my fall-back.
Either way, if you want your stored procedures to be able to pass back
useful information about problems or successes it encountered, I would
recommend using the return code and an @outputMessage output variable.
Your proc can test for the condition and set the output message into an
output variable and return the desired code. This way, if the database
driver provider or DBMS itself ever changes their error codes in a
future version it won't bork your code.
CREATE PROCEDURE myProc
@outputMessage varchar(255) OUTPUT,
@input1 int,
@input2 int
AS
BEGIN
IF badCondition is true
BEGIN
SET @outputMessage = 'Here is a detailed and useful error
message'
RETURN 75 -- Your special "code" for this error
END
-- otherwise...
SET @outputMessage = 'Execution Successful'
RETURN 0
END
The cfstoredproc tag give you the return code, and you can then check it
for whatever problems may have occurred. You can then use the value of
@outputMessage to return to the user if you so choose.
~Brad
-------- Original Message --------
Subject: Re: CFSTOREDPROC kind of sucks
From: Tony Bentley <[email protected]>
Date: Wed, August 26, 2009 3:04 pm
To: cf-talk <[email protected]>
Sorry, only two issues really.
A real world scenario is when a user tries to insert a value that must
be unique in the database. If a duplicate is found, SQL can return a
reference code and a message stating that there is a duplicate found, an
exception is thrown and an id is passed back - RAISERROR & @@ERROR.
When this is thrown, the message states "[Macromedia][SQLServer JDBC
Driver]" and then the RAISERROR message following. Nice for debugging
but not so nice for passing the message and error code to a handler.
I would really like to be able to use the error codes and have a
handler method based on which code it passed back. Not really possible.
Kind of hokey not to throw a SQL exception (and pass back a value
instead) since you would want SQL Server to log the exception.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325749
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
