Neil,
Ok - I will assume that none of your variables are actually numbers (cause
that would cause an error as well). I use the Dbvarname on IN variables as
well as out variables as in:
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@Sendall"
value="1">
<cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" dbvarname="@devid"
variable="Devid">
This code tells the DB which variable is which and tells me the variable
name that will store the return value (#devid#).
The other thing I noticed is that you are using CF_SQL_LONGVARCHAR instead
of CF_SQL_VARCHAR for some regular character fields. You might try
switching that. SQL server demands the variables be passed in the correct
order as well (as I'm sure you know). Finally, the other thing I see is your
syntax for your setup variables. It should be your create (or alter)
statement followed by your parameter list in parentesis as in the following
example:
ALTER PROCEDURE expense_summary_rpt_filtered
(
@from_dt datetime,
@to_dt datetime,
@uid integer,
@lname varchar(100) = NULL,
@fname varchar(100) = NULL,
@location varchar(50) = NULL,
@area varchar(50) = NULL,
@to_level varchar(50) = NULL,
@department varchar(50) = NULL,
@job varchar(50) = NULL,
@job_user1 varchar(50) = NULL,
@job_user2 varchar(50) = NULL,
@policy_nbr varchar(50) = NULL,
@respnse varchar(50) = NULL,
@overall_status varchar(50) = NULL,
@move_reason varchar(50) = NULL,
@new_state varchar(50) = NULL,
@area_desc varchar(50) = NULL,
@dept_desc varchar(50) = NULL,
@sort varchar(50) = NULL,
@sort2 varchar(50) = 'tid'
)
Hope this helps - sometimes these SP's require some trial and error.
-Mark
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
