asked on

calling stored proc from CF

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!

ASKER CERTIFIED SOLUTION

trailblazzyr55

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trailblazzyr55

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#"> 

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

ommer

ASKER

Cool! I like the 2nd way for its simplicity!