Naming stored proc parameters in ADO?

Question

Naming stored proc parameters in ADO?

Eric Mamet

SSChampion

Points: 11728
More actions
March 28, 2010 at 10:52 am

#75561

I am setting the recordset of a form dynamically though VBCode and just found out that my parameters are sent without name to my stored procedure despite me giving each parameter a name, like @Whatever
Is there a way to make ADO use the parameter name or am I bound to call my stored procedure with exactly the same parameters and in the same order as as it was defined?
Many Thanks
Eric

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 1

There is a way of doing it - you have to retrieve the parameters from your stored procedure with the Parameters.Refresh method, then populate them, then send them back - this is how you do it for a stored proc with two parameters - @BatchNo (numeric) and @DateUpdated (DateTime):

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = ActiveProject.Connection 'or your connection object

.CommandText = "dbo.usp_Whatever" 'Proc Name

.CommandType = adCmdStoredProc

.Parameters.Refresh

.Parameters("@BatchNo") = 1

.Parameters("@DateUpdated") = Format(Date, "dd/mm/yyyy")

.Execute

End With

Set cmd = Nothing

Eric Mamet SSChampion Points: 11728 More actions · Answer 2

Eric Mamet

SSChampion

Points: 11728

March 29, 2010 at 5:35 am

#1141325

OOhhh....

I like that!

Thanks

Rayis Imayev Old Hand Points: 300 More actions · Answer 3

Or you can run the dynamic SQL in your VB code and execute you stored procedure with explicit parameters naming:

EXEC sp_MyStoreProcedure @Parameter2 = 2, @parameter1 = GETDATE()

Eric Mamet SSChampion Points: 11728 More actions · Answer 4

I tried Parameters.Refresh but it fails all the time.

Apparently, when I do this, it seems to try and fire the stored procedure which does not work because some parameters are required...

Is there anything more to do for this to work?

Eric Mamet SSChampion Points: 11728 More actions · Answer 5

Dynamic SQL in VB is ok but then I can't retrieve any output parameter or return code from the procedure...

John Barnett SSCrazy Points: 2086 More actions · Answer 6

To pick up a return result set:

Dim rs As ADODB.RecordSet

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = ActiveProject.Connection 'or your connection object

.CommandText = "dbo.usp_Whatever" 'Proc Name

.CommandType = adCmdStoredProc

.Parameters.Refresh

.Parameters("@BatchNo") = 1

.Parameters("@DateUpdated") = Format(Date, "dd/mm/yyyy")

Set rs = .Execute

End With

Set cmd = Nothing

Eric Mamet SSChampion Points: 11728 More actions · Answer 7

If I declare my command as type adCmdStoredProc, then the problem is that ADO fires the parameters without name in the order they are declared.

This is manageable but very poor because likely to go wrong if someone changes the order of the parameters in the stored proc, which is quite legitimate.

To pick up the Return Status from the stored procedure, I just declare a @Return parameter of Direction InputOutput.

The problem is simply that the code has to specify the parameters in exactly the same order and not skip any, which is not too good.

If I try to put cmd.Parameters.Refresh, I always get a runtime error specifically on that line

John Barnett SSCrazy Points: 2086 More actions · Answer 8

For named parameters, put:

cmd.NamedParameters = True

in your VB/VBA code. You need to be using ADO 2.6 or newer for this to work.

Eric Mamet SSChampion Points: 11728 More actions · Answer 9

I like that!

I don't have control of the runtime environments...

How can I check whether my potential users have ADO 2.6?

Can I check this through trying to create some particular object in vba?

John Barnett SSCrazy Points: 2086 More actions · Answer 10

Tools -> References in the VBA editor will show you the current libraries in use (including the ADO version number).

John

Terry Auping SSC Rookie Points: 38 More actions · Answer 11

Instead of doing cmd.Parameters.Refresh try using:

Dim objParam as ADODB.Parameter

Set objParam = cmd.CreateParameter(Name, Type, Direction, Size)

cmd.Parameters.Append

objParam.Value = yourvalue

( ex: CreateParameter("@BatchNo", adInteger, adParamInput)

or: CreateParameter("@Return",adInteger,adParamOutput) )

repeat for each parameter in the SP

Eric Mamet SSChampion Points: 11728 More actions · Answer 12

This is what I first tried because I know this as "good practice" but ADO does not care and send the stored proc request without the parameter names... which fails if you did not declare exactly the same parameters in exactly the same order.

At the moment, I do declare all parameters in the right order but it sucks a little bit...

Terry Auping SSC Rookie Points: 38 More actions · Answer 13

hmm yes i see - that is annoying - the only thing I can think of is to create a 'wrapper' stored procedure that's tightly coupled to the ADO code which in turn executes the 'real' SP using named variables - that way if the order of variables in the 'real' SP should change it will not affect the ADO code.

Eric Mamet SSChampion Points: 11728 More actions · Answer 14

True.

A bit messy but it could help in some cases...

I'll try to find out if my customers have ADO 2.6 on their workstations...