Link to home
Start Free TrialLog in
Avatar of arduk
arduk

asked on

Calling Stored Proc. with optional parameter

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

ASKER CERTIFIED SOLUTION
Avatar of DialM4Monkey
DialM4Monkey

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arduk
arduk

ASKER

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
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.
Avatar of arduk

ASKER

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...
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