Access resultset value of cfprocresult name in cfif

Question

I am new to ColdFusion and want to rewrite my project from ASP.NET to learn ColdFusion step by step.

My SQL Server stored procedure for authentication (ASP.NET) looks like this:

CREATE PROCEDURE sp_auth
@suser  VARCHAR(20),
@spswd  VARCHAR(20)
AS
SELECT COUNT(*) FROM Users.SUsers WHERE suname=@suser AND supasswd=@spswd AND off_bit=0
GO

Application.cfc snippet.

<cfstoredproc procedure="sp_auth" datasource="cftraderelay">
<cfprocparam value="#cflogin.name#" cfsqltype="CF_SQL_VARCHAR" maxlength="20" type="in">
<cfprocparam value="#cflogin.password#" cfsqltype="CF_SQL_VARCHAR" maxlength="20" type="in">
<cfprocresult name="auth_pass" maxrows="1">
</cfstoredproc>


<cfif auth_pass GTE 1>
<cfloginuser name="#cflogin.name#" password = "#cflogin.password#" roles="#roles#"> 
<cfelse>
<cfoutput>
<h2>Your login information is not valid.<br> Please Try again</h2> 
</cfoutput>
<cfinclude template="login.cfm">
<cfabort>
</cfif>

What is right form of 'cfif auth_pass GTE 1' logic? How to access the value of 'auth_pass'. Cos I get "Complex object types cannot be converted to simple values" error by far.

Am I strictly supposed to use out type of cfprocparam in this case, not cfprocresult (modify SQL query for the OUTPUT variable)?

Can I access cfprocresult value right in the cfif statement?

You're right to use cfprocresult in your call to cfstoredproc. What comes back though is a query object. change your SQL to SELECT COUNT(*) AS auth_count ... then you can use auth_pass.auth_count in your code — barnyr
Not related to your question, but you have an odd combination. Your procresult specifies maxrows = 1 and your check looks for >= 1. Even if there are two records in the database, ColdFusion will only accept 1 because of the maxrows attribute. — Dan Bracuk
2 Dan Bracuk — The check looks for >= 1 as of result value "1" or greater, not as recordCount rows counter. It supposed to be always a single row statement. — 84RR1573R
And yeah, it could be and old ASP.NET misused feature as well. Better check for EQ 1 — 84RR1573R

Adam Cameron Adam Cameron · Accepted Answer · 2014-02-04T09:14:02

auth_pass contains a recordset, not a number, so you cannot compare it to 1. You perhaps want auth_pass.recordCount?

The best tool for examining the contents of a variable is <cfdump>. That and familiairising yourself with the docs for the code you're using, eg: <cfprocresult>, <cfstoredproc>, <cfquery> (the latter for details on query objects) if you run into problems and can't guess the solution.

Access resultset value of cfprocresult name in cfif

1 Answers