Why is calling a stored procedure always preceded by a call to sp_sproc_columns?

I've inherited a classic asp site, which uses ADO Db Command objects for stored procedures.

One of the things I've noticed, in the sql profiler, is that for every call to a stored procedure there is also a call to sp_sproc_columns. How can I prevent this call?

The code used to call the stored procedure开发者_JS百科s is in this form:

Dim objCmd
Set objCmd = server.CreateObject("adodb.command")
objCmd.ActiveConnection = oConn 'scoped globally
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "dbname.dbo.sprocName"

objCmd.Parameters.Append objCmd.CreateParameter ("@Paramater1", adInteger, adParamInput, , 123)
objCmd.Parameters.Append objCmd.CreateParameter ("@Paramater2", adInteger, adParamInput, , 456)

It turns out this is really simple. All you have to do is set the connection on the command object after you have set the parameters. Like so:

Dim objCmd
Set objCmd = server.CreateObject("adodb.command")
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "dbname.dbo.sprocName"
objCmd.Parameters.Append objCmd.CreateParameter ("@Paramater1", adInteger, adParamInput, , 123)
objCmd.Parameters.Append objCmd.CreateParameter ("@Paramater2", adInteger, adParamInput, , 456)
objCmd.ActiveConnection = oConn 'set after the command object has been fully configured.

I'd be inclined to say that you can't in the general case, the documentation for sp_sproc_columns states that it is required to get the columns that will be returned by the stored procedure which I'd assume is needed by ADODB to return columns with the appropriate data types.

In the specific case of a known stored procedure there's an interesting mailing list discussion that suggests that you can avoid the call if you are totally specific about the parameters.

The documentation for ADODB.Command would seem to support this suggestion, I've highlighted a couple of relevant parts:

Using the Refresh method on a Command object's Parameters collection retrieves provider parameter information for the stored procedure or parameterized query specified in the Command object. Some providers do not support stored procedure calls or parameterized queries; calling the Refresh method on the Parameters collection when using such a provider will return an error.

If you have not defined your own Parameter objects and you access the Parameters collection before calling the Refresh method, ADO will automatically call the method and populate the collection for you.

You can minimize calls to the provider to improve performance if you know the properties of the parameters associated with the stored procedure or parameterized query you wish to call. Use the CreateParameter method to create Parameter objects with the appropriate property settings and use the Append method to add them to the Parameters collection. This lets you set and return parameter values without having to call the provider for the parameter information. If you are writing to a provider that does not supply parameter information, you must manually populate the Parameters collection using this method to be able to use parameters at all. Use the Delete method to remove Parameter objects from the Parameters collection if necessary.

Hope this answers your question