Calling Stored Proc. with optional parameter

arduk
arduk used Ask the Experts™
on
I have an Access 2000 ADP that is linked to an SQL server database. I am trying to execute a stored proc. that has parameters with default values specified from within VBA code.

I was under the impression that if I specify which parameters I am adding by using the ado command object, that I would not have to pass in all of the parameters, rather I could specify the name of the parameter, and its value, and it would be set. When I try to do this however, if I have 5 parameters, and set 3 of them using the createparameter, it is the first 3 that are set, not the named ones that I specify.

Do I have the wrong impression about how this works, or have I got a problem with my code??

I am using the commands as shown below...

stored proc defined as:
Create proc SP (@value1 int = 0, @value2 int = 1, @value3 int = 2)

VB CODE:
Set adoCmd = CreateObject("ADODB.Command")
adoCmd.CommandText = strProcName
adoCmd.CommandType = adCmdStoredProc
Set objNewParam = adoCmd.CreateParameter("value1", adinteger, adparaminput, , 6)
adoCmd.Parameters.Append objNewParam
Set objNewParam = adoCmd.CreateParameter("value3", adinteger, adparaminput, , 100)
adoCmd.Parameters.Append objNewParam

This appears to set the parameters @value1 and @value2, not @value1 and @value3 as desired...

Any help greatly appreciated...

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would say that you have the wrong impression.  The SQL proc knows the names of the params once its inside.  but ADO doesn't know the names of the params only the order.  In fact the "name" of the param is optional in ADO.  I'd bet that the name property is used when you need to refer to the param again in ADO (before executing the proc)

Author

Commented:
Actually, have just found out that it can be done, but you need to set the parameter by name, not using the append method:

adocmd.parameters("@Value1") = 6
adocmd.parameters("@Value3") = 100

Using this method, the command object queries the server to find out the parameters. downside is an extra round trip to the server, so is a bit slower than using the append method I was initially using :(

Thanks for your comments anyway

Commented:
You can avoid this by getting rid of parameters completely.  Create a string that contains your command, exactly as if you were exucuting it manually using ISQL or something similar...

    dim sSSQL as string
    sSQL = "exec PROC_NAME @value1=6, @value3=100"

Now just execute it using your connection object.  Assuming that is called conDB....

    conDB.execute sSQL

That should save you the access time, and more importantly gives you a SQL string that you can debug easily and execute directly against the database if there are problems, rather than figuring out what might (or might not) be held in the parameters.

Author

Commented:
That is how I used to execute stored procs, however someone suggested that this was not the most efficient way of doing it?
Using the addparameter method also gives you access to any output parameters there may be as well...

Commented:
There's no difference in the two execution methods, other than the ability to use output parameters (as you say).  

I've been supporting and coding this type of connection for over five years in Investment banks now and the worst thing to find when something goes wrong in someones code is parameters.  Even output paramaters can easily be simulated by just returning the data as a resultset instead and at 3am I'd much rather have a simple SQL string that I can run manually through ISQL than a set of parameters that I have to decode before even being able to test the sql directly.

Everyone has different opinions on this but most of the people I've spoken to on the sharp end agree with me...

I think that either DialM4Monkey's solution or mine should be enough to answer your question so can you assign the points or post what extra info you need?

Cheers,

Simon

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial