Solved

calling stored proc from CF

Posted on 2006-10-26
3
175 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
  • 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now