ColdFusion: returnCode Attribute in <cfstoredproc>

Have you ever wondered how to use returnCode attribute in <cfstoredproc>? If returnCode is set to true, it will return a status code from SQL RETURN statement. The status code could be an SQL error number, number of affected rows, or any integer number that is meaningful for you.

There are two ways to retrieve status code:

• cfstoredproc.statusCode
• result_var.statusCode --result_var is defined in result attribute

Even though both ways will generate the same result, it is better to use result attribute because if you dump the variable, the structure will give you an extra info. about caching.

Tip: Even though you do not have return code in your stored proc., turning on returnCode will not hurt. Later you can always go back to your stored proc. to add return code.

-- This stored proc. demonstrates how we could use RETURN statement.
CREATE PROCEDURE spu_customer_update
 @customer_id INT = 0
 , @email  VARCHAR(100) = ''
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @updated_rowcount INT = 0;
 
 IF @customer_id <> 0 AND @email <> ''
 BEGIN
  UPDATE customer
  SET email = @email
  WHERE customer_id = @customer_id;
  
  -- @@ROWCOUNT returns the number of rows affected by the last statement.
  -- If the UPDATE statement success, @@ROWCOUNT will be 1.
  SET @updated_rowcount = @@ROWCOUNT;
 END;
 
 RETURN @updated_rowcount;
END;

<cfstoredproc 
  procedure="spu_customer_update" 
  datasource="mydatabase"
  result="proc_result"
  returnCode="true">
 <cfprocparam cfsqltype="cf_sql_integer" value="12345" />
 <cfprocparam cfsqltype="cf_sql_varchar" value="myemail@email.com" />
 
</cfstoredproc>

<cfdump var="#proc_result#" />

References:

• @@ROWCOUNT (Transact-SQL)
• cfstredproc