Accessing RAISEERROR message from cfstoredproc
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.
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>
Have you tried cfprocresult? Manual page says:
Associates a query object with a result set returned by a stored procedure.
精彩评论