Bi-directional SP parameters with default values
Posted on 2001-08-03
After spending the day causing Access Violations on our development machine, it's time to call in the cavalry :)
I have this (test) stored proc (SQL Server 7.0 SP2):
CREATE PROCEDURE spTestOutput (@testint int = NULL OUTPUT, @testvarchar varchar(50) = NULL OUTPUT) AS
IF @testint IS NULL
SET @testint = Cast(@testvarchar AS int)
IF @testvarchar IS NULL
SET @testvarchar = Cast(@testint AS varchar(50))
I want to call this using an ADO Command Object. The plan is that I pass one of the two parameters, which comes back (converted to the right datatype) in the other one as an output parameter. IOW, I need a method to create an adParamInputOutput parameter that will take the default value as defined in the stored proc if no value is given from the client side. This is what I have up to now (VB 6.0 SP5):
Set con = New ADODB.Connection
Set com = New ADODB.Command
.Provider = "sqloledb"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.ConnectionString = "Server=MyServer;Database=MyDB;UID=MyUser;PWD=MyPassword;"
.Name = "spTestOutput"
.CommandText = "spTestOutput"
.CommandType = adCmdStoredProc
.NamedParameters = True
Set .ActiveConnection = con
.Parameters.Append .CreateParameter("@testint", adInteger, adParamInputOutput, , Null)
.Parameters.Append .CreateParameter("@testvarchar", adVarChar, adParamInputOutput, 50, Null)
.Execute , , adExecuteNoRecords
Debug.Print "@testint = " & CStr(.Parameters("@testint"))
Debug.Print "@testvarchar = '" & .Parameters("@testvarchar") & "'"
The problem is that the default value for the parameter is given in the CreateParameter definition in the VB code. This means that if I want to change the default value for the parameter later, I have to recompile and re-distribute my code. What I want is that changing the default value in the stored proc will suffice.
I already tried simply omitting the default value in the CreateParameter code (after all it is an optional parameter), but that is what's causing the Access Violation in SQL Server (Profiler shows that the stored proc executes fine, the AV is only caused when SQL Server tries to return the results).
Note: I do NOT want to use the Refresh method on the Parameters collection - which does seem to work. The reason for this is that this test will eventually be incorporated in an object model that needs to create a _lot_ of command objects with multiple bi-directional parameters each. If I would have to use the Refresh method on all those Command Objects, simply instantiating an object from this class would take tens of seconds, which is unacceptable.
Hope that's clear enough,