We help IT Professionals succeed at work.

calling stored proc from CF

ommer
ommer asked
on
Medium Priority
232 Views
Last Modified: 2013-12-24
Hi, dear CF experts,

This my first sp call from CF. I got Invalid CFML construct on last CFIF statement. Here is my cf code and sp itself, which I have tested successfully in sql.

<cfstoredproc datasource="QWSQL" procedure="Web_CheckLastLogin" returncode="No">
  <cfprocresult name="MonthSinceLastLogin" resultset="1">
<CFPROCPARAM TYPE="IN"
    DBVARNAME="@p1"
    VALUE=login.user_id
 CFSQLTYPE="CF_SQL_LONGVARCHAR">
<CFPROCPARAM TYPE="OUT"
    DBVARNAME="@MonthSinceLastLogin"
    Variable = m_Months
    CFSQLTYPE="CF_SQL_SMALLINT">
</cfstoredproc>
<cfif  m_Months LG 2>
     <cflocation url ="MaintainAcct.cfm?user_id>
</cfif>

CREATE PROCEDURE [dbo].[Web_CheckLastLogin]
       @p1 varchar(10) ,
@MonthSinceLastLogin smallint output
AS
SET NOCOUNT ON
select @MonthSinceLastLogin = datediff(mm,  
(select max(time_occured) from web_history where script_name ='/cust/login.cfm'
and [user_id] =@p1),
getdate())
return @MonthSinceLastLogin
SET NOCOUNT OFF
GO

Thanks a lot!
Comment
Watch Question

you had some syntax errors... try this...

<cfstoredproc datasource="QWSQL" procedure="Web_CheckLastLogin" returncode="No">
  <cfprocresult name="MonthSinceLastLogin" resultset="1">
  <CFPROCPARAM TYPE="IN" DBVARNAME="@p1" VALUE="#login.user_id#" CFSQLTYPE="CF_SQL_LONGVARCHAR">
                                                                  <!--- ------------------^ here ----- --->
  <CFPROCPARAM TYPE="OUT" DBVARNAME="@MonthSinceLastLogin" Variable="m_Months" CFSQLTYPE="CF_SQL_SMALLINT">
                                                                                       <!--- ---------------and---^ here ----- --->
</cfstoredproc>
<cfif  m_Months LG 2>
     <cflocation url ="MaintainAcct.cfm?user_id>
</cfif>

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
You can also called a stored proc like so.....
-------------------------------------------------------------
<cfquery datasource="YourDSN" name="MyQuery">
   {call dbo.Web_CheckLastLogin ('#login.user_id#')}
</cfquery>


<cfdump var="#MyQuery#"> <!--- dump to see results --->





and have your stored procedure like so....
-------------------------------------------------------------
CREATE PROCEDURE [dbo].[Web_CheckLastLogin]
      @p1 varchar(10)
AS
SET NOCOUNT ON
DECLARE @MonthSinceLastLogin smallint
select @MonthSinceLastLogin = datediff(mm,(select max(time_occured) from web_history where script_name ='/cust/login.cfm' and [user_id] =@p1),getdate())
SET NOCOUNT OFF
GO

Author

Commented:
Cool! I like the 2nd way for its simplicity!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.