Accessing RAISEERROR message from cfstoredproc

Question

I have a SQL stored procedure which under some situations will return a result of -1 if it fails, but also returns a message via the RAISERROR command e.g.:

BEGIN

    RAISERROR ('Error %i has occurred', 11, 1, 0)
    RETURN -1

END

I am accessing this via coldfusion using cfstoredproc e.g.:

<cfstoredproc procedure="sp_return" datasource="myDatasource" returncode="yes">
<cfdump var="#cfstoredproc#">

But the structure returned only contains an ExecutionTime and StatusCode keys. Is there any way I can access the error message which has been returned. e.g. To display to the user.

Thanks,

Tom

p.s. I would tag with "cfstoredproc" but I can't create new tags.

Answer 1

Not sure what DB you use but with Oracle I just use ColdFusion Exceptions to bubble up the Oracle exceptions. - #cfcatch.message# and #cfcatch.detail# are what you want to echo to the user.

<cftry>

<cfstoredproc  procedure = "my_Proc" dataSource = "#DB#" returnCode = "No">
   <cfprocparam type="in" cfsqltype="CF_SQL_VARCHAR" variable="myvar"   value="#someval#" null="No"> 
   <cfprocresult name="my_Response">
</cfstoredproc>

<cfcatch type="any">

   <cflog file="ProcError" text="Message = #cfcatch.message# Detail= #cfcatch.detail#">

</cfcatch>
</cftry>

Answer 2

Have you tried cfprocresult? Manual page says:

Associates a query object with a result set returned by a stored procedure.