That is very nice Brad. Thanks for that perspective.
>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 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:325780 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
