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

T-SQL: Useful Tips

SCOPE_IDENTITY()	To retrieve identity value from the last inserted record, use SCOPE_IDENTITY() instead of @@IDENTITY.
EXISTS	To gain better performance, use EXISTS instead of IN. BONUS: SQL Server: JOIN vs IN vs EXISTS - the logical difference
SET NOCOUNT ON	Use it in a stored proc. It reduces network traffic by eliminating sending "done" messages to client.
@@ROWCOUNT	It returns number of rows affected by the last statement.
RETURN	It is usually used for returning status/error code. RETURN can be used to exit from a stored proc. Any statements that follow RETURN are not executed.
@@ERROR	It returns the error number for the last statement. Zero (0) means no errors.
ERROR_NUMBER()	The result of ERROR_NUMBER() is the same as @@ERROR. BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH -- All of these must be used inside CATCH. SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

T-SQL: Variable Assignment Using SET vs. SELECT

Use SET instead of SELECT to make a variable assignment because it is ANSI standard and recommended in SQL Server doc.

References:

• SET @local_variable (Transact-SQL)
• T-SQL: SET vs SELECT when assigning variables
• Differences between SET and SELECT in SQL Server

-- You could assign variable with SELECT.
DECLARE @total_rows INT = 0;
SELECT @total_rows = COUNT(*) FROM customer;
PRINT @total_rows;

-- However, it is better to use SET.
DECLARE @total_rows INT = 0;
SET @total_rows = (SELECT COUNT(*) FROM customer); -- Parentheses around SELECT statement is required.
PRINT @total_rows;