MSSQL Stored Procedure Problem

[Sequenzia]

I am trying to pass some variables into a MSSQL stored procedure and for some
reason it is truncating the last variable. If I move the order of the variables
in the stored procedure it always drops the last one. I have done this plenty
of times without problems, but for some reason it is not working with this
code. Here is what I have, any help would be wonderful.

CF Code:

<cfstoredproc datasource="crup" procedure="sp_rate">

<cfprocparam value="#gs_limits#" dbvarname="@gs_limits"
cfsqltype="cf_sql_integer">
<cfprocparam value="#gs_deductible#" dbvarname="@gs_deductible"
cfsqltype="cf_sql_integer">
<cfprocparam value="#schcredit#" dbvarname="@schcredit" cfsqltype="varchar">
<cfprocparam value="#perss#" dbvarname="@perss" cfsqltype="cf_sql_decimal"
scale="3">
<cfprocparam value="#ghp#" dbvarname="@ghp" cfsqltype="cf_sql_decimal"
scale="3">
<cfprocparam value="#posttreats#" dbvarname="@posttreats"
cfsqltype="cf_sql_decimal" scale="3">
<cfprocparam value="#pretreats#" dbvarname="@pretreats"
cfsqltype="cf_sql_decimal" scale="3">
<cfprocparam value="#lopayroll#" dbvarname="@lopayroll"
cfsqltype="cf_sql_decimal" scale="3">
<cfprocparam value="#renwalreceipts#" dbvarname="@renwalreceipts"
cfsqltype="cf_sql_decimal" scale="3">
<cfprocparam value="#inspections#" dbvarname="@inspections"
cfsqltype="cf_sql_decimal" scale="3">
<cfprocparam value="#cm#" dbvarname="@cm" cfsqltype="cf_sql_decimal" scale="3">
<cfprocparam value="#cmper#" dbvarname="@cmper" cfsqltype="cf_sql_decimal"
scale="3">

<cfprocresult name="rate_out">

</cfstoredproc>

SQL Stored Procedure:

CREATE PROCEDURE [sp_rate]

@gs_limits int,
@gs_deductible int,
@schcredit decimal(18,3),
@cmper decimal(18,3),
@cm decimal(18,3),
@perss decimal(18,3),
@ghp decimal(18,3),
@posttreats decimal(18,3),
@pretreats decimal(18,3),
@lopayroll decimal(18,3),
@fume decimal(18,3),
@inspections decimal(18,3),
@renwalreceipts decimal(18,3)

AS

/* -------------------------------------------------- Common Variables
------------------------------------------------- */


declare @expfactor decimal(18,3)
set @expfactor = '1.00'

declare @ssper decimal(18,3)
set @ssper = @perss * '.01'

declare @percm decimal(18,3)
set @percm = @cmper * '.01'


/* -------------------------------------------------- Rates
---------------------------------------------------------------------- */

declare @extrate decimal(18,3)
select @extrate = rate from rate
join classcode on classcode.classcodeid = rate.classcodeid
where rate.classcodeid = '1' and limitid = @gs_limits


declare @lorate decimal(18,3)
select @lorate = rate from rate
join classcode on classcode.classcodeid = rate.classcodeid
where rate.classcodeid = '2' and limitid = @gs_limits

declare @fumerate decimal(18,3)
select @fumerate = rate from rate
join classcode on classcode.classcodeid = rate.classcodeid
where rate.classcodeid = '3' and limitid = @gs_limits

declare @ssrate decimal(18,3)
select @ssrate = rate from rate
join classcode on classcode.classcodeid = rate.classcodeid
where rate.classcodeid = '4' and limitid = @gs_limits

/* -------------------------------------------------- Deductibles
------------------------------------------------------- */

declare @extdeductdis decimal(18,3)
select @extdeductdis = discount from deductiblediscount
where classcodeid = '1' and deductiblediscount.deductibleid = @gs_deductible

declare @lodeductdis decimal(18,3)
select @lodeductdis = discount from deductiblediscount
where classcodeid = '2' and deductiblediscount.deductibleid = @gs_deductible

declare @fumedeductdis decimal(18,3)
select @fumedeductdis = discount from deductiblediscount
where classcodeid = '3' and deductiblediscount.deductibleid = @gs_deductible


/* -------------------------------------------------- Exterm
------------------------------------------------------------ */

declare @extfrate decimal(18,3)
set @extfrate = (@extrate - @extdeductdis) * @schcredit * @expfactor


/* -------------------------------------------------- Ext GHP
----------------------------------------------------------- */

declare @ghpexp decimal(18,3)
set @ghpexp = @ghp * (1 - @ssper)

declare @ghpprem decimal(18,2)
set @ghpprem = @extfrate * @ghpexp


/* -------------------------------------------------- Ext Pre Treats
----------------------------------------------------- */


declare @preexp decimal(18,3)
set @preexp = @pretreats * (1 - @ssper)

declare @preprem decimal(18,2)
set @preprem = @extfrate * @preexp


/* -------------------------------------------------- Ext Post Treats
----------------------------------------------------- */


declare @postexp decimal(18,3)
set @postexp = @posttreats * (1 - @ssper)

declare @postprem decimal(18,2)
set @postprem = @extfrate * @postexp


/* -------------------------------------------------- Ext Total Prem
------------------------------------------------------- */

declare @totalextprem decimal(18,2)
set @totalextprem = @ghpprem + @preprem + @postprem


/* -------------------------------------------------- LO
-------------------------------------------------------------------- */


declare @lofrate decimal(18,3)
set @lofrate = (@lorate - @lodeductdis) * @schcredit * @expfactor


declare @totalloprem decimal(18,2)
set @totalloprem = @lofrate * @lopayroll


/* -------------------------------------------------- Fume
-------------------------------------------------------------------- */


declare @fumefrate decimal(18,3)
set @fumefrate = (@fumerate - @fumedeductdis) * @schcredit * @expfactor

declare @fumeexp decimal(18,3)
set @fumeexp = @fume * (1 - @ssper)

declare @totalfumeprem decimal(18,2)
set @totalfumeprem = @fumefrate * @fumeexp


/* -------------------------------------------------- S&S
---------------------------------------------------------------------- */


declare @ssfrate decimal(18,3)
set @ssfrate = (@ssrate - @fumedeductdis) * @schcredit * @expfactor

declare @ssghp decimal(18,3)
set @ssghp = @ghp * @ssper

declare @ssfume decimal(18,3)
set @ssfume = @fume * @ssper

declare @sspre decimal(18,3)
set @sspre = @pretreats * @ssper

declare @sspost decimal(18,3)
set @sspost = @posttreats * @ssper

declare @ssrenew decimal(18,3)
set @ssrenew = @renwalreceipts * .25

declare @ssinspect decimal(18,3)
set @ssinspect = @inspections * .25

declare @sscontmon decimal(18,3)
set @sscontmon = @cm * @percm


declare @ssexp decimal(18,3)
set @ssexp = @ssghp + @ssfume + @sspre + @sspost + @sscontmon


declare @ssprem decimal(18,3)
set @ssprem = @ssfrate * @ssexp



/* -------------------------------------------------- Output
------------------------------------------------------------------ */


select @totalextprem as totalextprem, @totalloprem as totalloprem,
@totalfumeprem as totalfumeprem, @ssprem as ssprem
GO

Web Page Error Message:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Procedure 'sp_rate' expects
parameter '@renwalreceipts', which was not supplied.

The error occurred in C:\Data\CRUP\rate_output.cfm: line 40
Called from C:\Data\CRUP\rate_output.cfm: line 1
Called from C:\Data\CRUP\rate_output.cfm: line 40
Called from C:\Data\CRUP\rate_output.cfm: line 1

38 : <cfprocparam value="#cmper#" dbvarname="@cmper"
cfsqltype="cf_sql_decimal" scale="3">
39 :
40 : <cfprocresult name="rate_out">
41 :
42 : </cfstoredproc>

[philh]

Have you tried placing the procparams in the exact order that they're expected in the SP?

[paross1]

To amplify what Phil Hegedusich said, you must place your CFPROCPARAM tags in
the same order as they appear in your stored procedure spec, as the dbvarname
attribute has been depricated in CF MX (unless you are using an older versin of
CF).

[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b16.htm#wp1102102[/url]
Changed the dbvarname attribute behavior: it is now ignored for all drivers.
ColdFusion MX uses JDBC 2.2 and does not support named parameters.

Phil