Solved

calling stored proc from CF

Posted on 2006-10-26
3
182 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!
0
Comment
Question by:ommer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 125 total points
ID: 17815294
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>
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17815345
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
0
 

Author Comment

by:ommer
ID: 17821527
Cool! I like the 2nd way for its simplicity!
0

Featured Post

Don't miss ATEN at NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
What You Need to Know when Searching for a Webhost Provider
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question