I had similar problems. After much frustration I found that somewhere along
the line SQL Server ignores the DBVARNAME parameter, so parameters are
passed in the order they are given. Thus the first CFPROCPARAM is passed as
the first sp parameter, etc, regardless of any DBVARNAME settings. I suspect
that this is where you're getting your numeric value error, too.
e.g.
...in SQL Server...
CREATE PROCEDURE update
@p_ref INT = -1,
@p_name VARCHAR(80) = "Name",
@p_description VARCHAR(250) = "Description"
AS
-- Some useful stuff
GO
...in CF...
<CFSTOREDPROC PROCEDURE="update" DATASOURCE=#Application.DataSource#>
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_INTEGER VALUE=1
DBVARNAME="p_ref">
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_VARCHAR VALUE="Foo"
DBVARNAME="p_name">
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_VARCHAR VALUE="Bar"
DBVARNAME="p_description">
</CFSTOREDPROC>
Works okay (@p_ref => 1, @p_name => "Foo", @p_description =>"Bar")
but
<CFSTOREDPROC PROCEDURE="update" DATASOURCE=#Application.DataSource#>
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_VARCHAR VALUE="Foo"
DBVARNAME="p_name">
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_VARCHAR VALUE="Bar"
DBVARNAME="p_description">
</CFSTOREDPROC>
Will fail with a cast conversion error! (@p_ref => "Foo", @p_name => "Bar",
@p_description => "Description")
also
<CFSTOREDPROC PROCEDURE="update" DATASOURCE=#Application.DataSource#>
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_INTEGER VALUE=1
DBVARNAME="p_ref">
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_VARCHAR VALUE="Bar"
DBVARNAME="p_description">
<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_VARCHAR VALUE="Foo"
DBVARNAME="p_name"> </CFSTOREDPROC>
Will work, but not behave how you expect (@p_ref => 1, @p_name => "Bar",
@p_description =>"Foo")
The solution is to put the procedure call into it's own file, use CFPARAMs
to set up the defaults and call the stored proc with all the parameters (in
the correct order). Then whenever you want to call the stored proc use
<CFMODULE> to call the file, passing only the parameters you want to set.
-----Original Message-----
From: Levi Wallach [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 26, 2000 4:42 AM
To: [EMAIL PROTECTED]
Subject: SQL Server 7 stored procedures driving me crazy!
So I thought I had solved my problem of consolidating a lot of my stored
procedures into one. I found you could specify a default value for a
parameter so in case one template didn't specify it, it wouldn't throw an
error. Great, that worked, but then I went to the template that did specify
it via an input parameter. Well, here's where my troubles began. I
actually had specified two parameters in the stored procedure, one after the
other, but in this particular template, I only needed to transfer a value
for one of them. Well, despite passing the value, it only used my default
value in the stored procedure. So, I switched the order of the variables in
the stored procedure and vuala! It seems that if the first one was not
found, it assumed the second one would not be found either! How does this
make sense??? Also one of my templates now simply does not work with this
stored procedure. It gives me a "Numeric value out of range" error, though
when I put the same exact sql statement into a query (this particular sql
statement actually doesn't use any input parameters), it works fine! Ugh!
It looks like I am going to have to go back and pass my own default values
in every template via cfprocparam tags, how annoying. Unless of course I've
completely missed something...
Levi
------------------------------------------------------------------------
| Levi Wallach - [EMAIL PROTECTED] P:(703) 237-0443 F: 703-237-3490 |
| Senior Webmaster, Tran Interactive Design Group - http://www.tidg.com |
------------------------------------------------------------------------
| Creator, DVDMON.com - http://www.dvdmon.com - [EMAIL PROTECTED] |
| DVD News, Views & Reviews, Music & Book Reviews, PDA Channel Content, |
| Contests, DVD Buying Guide and more! |
-------------------------------------------------------------------------
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
