[thelist] CF/SQL Server 2k Stored Procedures

[Joshua Olson]

Hi Gang,

I recently upgraded a site from a Sybase Database to SQL Server 2k.  I used
a number of stored procedures and called these stored procedures using
<cfstoredproc>.  After upgrading the SQL Server 2k I noticed my code broke.
The stored procedures suddenly because very sensitive to missing attributes
and the ordering of the attributes.  For example:

CREATE PROCEDURE sp_myproc
    @mystring varchar(100) = null,
    @mynumber int = null
  AS
    --Do something cool here, like return a resultset
GO

The following code would fail:

<cfstoredproc procedure="sp_myproc" datasource="myDS">
  <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@mynumber"
value="1234" null="No">
  <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@mystring"
value="foo" null="No">
  <cfprocresult name="get_things">
</cfstoredproc>

It would fail until I reversed the order of the cfprocparam tags, making
this:

<cfstoredproc procedure="sp_myproc" datasource="myDS">
  <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@mystring"
value="foo" null="No">
  <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@mynumber"
value="1234" null="No">
  <cfprocresult name="get_things">
</cfstoredproc>

Does anybody know why the interface between Cold Fusion and MS SQL Server 2k
is so sensitive when calling Stored Procedures?  Is there some way to fix
this annoyance?

TIA,
-joshua