2

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.

ale
  • 6,369
  • 7
  • 55
  • 65
Loftx
  • 1,760
  • 4
  • 29
  • 50

2 Answers2

3

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>
kevink
  • 1,958
  • 3
  • 14
  • 14
  • I'm using SQL Server 2005. When I try wrapping a cftry/cfcatch block around my code it makes no difference - no coldfusion error is thrown so there's nothing to catch I guess. – Loftx Dec 10 '09 at 17:54
  • I confirm that using SQL Server 2005 and Adobe CF 8 a DO recieve raised error in cfcatch.Detail: [Macromedia][SQLServer JDBC Driver][SQLServer]Error 0 has occurred – Sergey Galashyn Dec 10 '09 at 18:37
  • @Loftx - Seems odd but there is a setting in "Debug Output Settings" under "Custom Debugging Output" about showing exception info in debugging. I use the technique in servers with robust debugging off but that field may need to be checked. Is it on your box? – kevink Dec 10 '09 at 20:23
  • That severity 11 in RAISERROR is important; ColdFusion seems to ignore anything 10 and under, but 11 will cause an exception. – CrazyPyro Jun 15 '11 at 00:54
1

Have you tried cfprocresult? Manual page says:

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

Sergey Galashyn
  • 6,946
  • 2
  • 19
  • 39
  • Hi, I tried the query in my original response with a cfprocresult. When dumping out this variable I get a variable undefined error. From reading the docs it looks like this is for returning querys whereas my stored procedure returns -1 and the error message. – Loftx Dec 10 '09 at 17:53
  • See, you have to do the SELECT to populate this variable, for example "SELECT 'this is error text' AS error". I'm not a SQL server guru, but I suppose it is possible to handle the error in the procedure and return the result using SELECT. – Sergey Galashyn Dec 10 '09 at 18:43