 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ColdFusion / Advanced Techniques / September 2004Stored Procedure
Why cant I get cf to call stored procedures with optional parameters??? sql server2000 The stored procedure works fine, it has a few optional parameters. If I call the procedure with all of the parameters, it works fine. If leave one of the parameters out. Which is an optional parameter. I get an error... Attribute validation error for tag cfoutput. The value of the attribute query, which is currently "getAuditsData", is invalid. What database are you using? If it is Oracle, you will get an error like PLS-00306: wrong number or types of arguments in call.... if you leave off a parameter, even if it is "optional" (by this I assume that you mean it has a default value). Just add a cfprocparam for the "optional" parameter and set it to NULL. Plus, the parameters must be in the same order as they are declared in the stored procedure. Phil lenga: The CFSTOREDPROC tag does pass each CFPROCPARAM to the SQL server in the order that you put them in the code, so if you leave one of 'em out, the SQL code will be confused as to which passed value (from the CF code) corresponds with which SQL variable in your stored procedure. As Phil mentioned, you need to pass some kind of value via a CFPROCPARAM tag, even if it's a NULL or default value. BTW, I am really stuck on attempting to use an Informix stored procedure to return a recordset to CF page. Does anyone reading this thread have advice to offer? Please reply to this thread or email mstiffler@ricochetinteractive.com off-list. Thanks! Thanks guys, Yea the only way that I have been able to work through this by passing in NULL values. But that just makes the whole optional parameter thing useless. If I send a NULL value in will it even take the default value? Would I pass a NULL for an INTEGER? My real problem is that I want to add functionality to an already used SP without having to alter any of the existing code which calls the SP. But by having to include even the optional parameters I have to go and edit all of the code in existence that utilizes this stored procedure. I am a C++/Java developer who is really a newbee to CF. In C++ I wouldn't even have to worry about it. It just seems as if there has to be a way to do this. If it were me, I might opt to make a new stored procedure with the added functionality and then add it to my CF code on an ad hoc basis, only in the CF files that absolutely need it. If you have a lot of CF code to scour to find all references to your existing sproc, then I can see what a headache that might cause. I believe what we're working with here is a limitation of the ODBC capabilities regarding allowing the communication between CF and SQL. As I mentioned in one post, I'm having serious trouble with that same limitation using an Informix db, so I feel your pain... I suppose that you could try to "overload" your stored procedure with another copy containing the parameters that match your list, but that can get real nasty, and it also depends on what database you are using, and drivers, etc. I have had problems calling home brewed overloaded PL/SQL procedures from CF, so I just bite the bullet and make sure that I add new parameters to all of my CF proc calls if I add another parameter to the associated stored procedure. Phil |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.