Defaulted Input Param after an Output Param

[Jen526]

I suspect this belongs in the ColdFusion forum, but I don't know CF, so thought I'd try here.

I'm a VB/SQL programmer providing support to a Cold Fusion developer creating CF front-ends that hook into our existing stored procs.

I have a stored proc set up something like:

CREATE proc MyProcName
  @input1 varchar(10),
  @input2 int, 
  @output1 int out,
  @NEWInput3 int=null

NewInput3 is a new parameter I've added ONLY for this new development that will have no bearing on my existing apps, so I added the default to avoid having to change my existing VB apps.

The ColdFusion developer is calling the procedure:

<cfstoredproc procedure="MyProcName" dataSource="MyDSN"> 
<cfprocparam type="IN" dbvarname="@input1" value="#value1#" cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="IN" dbvarname="@input2" value="12345" cfsqltype="CF_SQL_INT"> 
<cfprocparam type="OUT" dbvarname="@output1" variable=varOut cfsqltype="CF_SQL_INT"> 
<cfprocparam type="IN" dbvarname="@NEWInput3" value="1" cfsqltype="CF_SQL_INT">
</cfstoredproc>

And getting an error: [Macromedia][SQLServer JDBC Driver][SQLServer]Formal parameter '@NEWInput3' was defined as OUTPUT but the actual parameter not declared OUTPUT.

If we move NewInput3 above the outgoing param, it works. If we remove the "=Null" default from NewInput3, it works. Both would require me to make a VB change I'd rather avoid.

Can anyone tell me if I'm doing something illegal in my stored proc definition that SQL is forgiving of but ColdFusion is not? Or does this seem like it would be more a CF question? I know exactly ZIP about CF.

Any suggestions will be GREATLY welcome.
Jen

 

[donutman]

Well, since any suggestions are welcome, why not create a new SP for the CF folks and leave your VB stuff intact. BTW, that's an interesting tip to the VBers. I would have guessed that adding a new parameter would have crashed any use of the SP after a Parameter.Refresh.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I know nothing about Cold Fusion, but has he tried changing the order he does the variables (without you changing the sp). Since he is calling them with their attached variable names, he should be able to put them in any order. Maybe if he always puts the output variable last, this will work? It's worth a try anyway.

Or you could just give him his own sp which is not called by your vb app. Then you can do whatever you like in it and can change the order of the variables without affecting your own application. I did this a lot when we were switching the fornt end on one database because the new front end had slightly different requirements. Even better if you give all the sps a name which indicates the frontend and you give him a new sp for every function, then when you turn off the old one, you know exactly which sps are no longer being used.

Questions about posting. See faq183-874

 

[Jen526]

The CF app is not replacing the VB app, just providing a different interface for web users who don't need the full product. We're constantly customizing things for this particular customer as the whim strikes them, so the more stored procs we have doing the same thing, the more likely it is some change won't get made in *all* the places it's needed.

Hmmmm...maybe I can get by with giving them their own stored proc that does NOTHING but call the old proc, since SQL doesn't seem to have any hangups about my parameter order. It's kludgy, but it'd keep the code in one place, at least.

Thanks for getting me thinking down that path. I'd still be interested in knowing why CF seems to have a problem with the way I was defining things, though...