Solved

Bi-directional SP parameters with default values

Posted on 2001-08-03
16
438 Views
Last Modified: 2013-12-25
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)
    ELSE
        IF @testvarchar IS NULL
            SET @testvarchar = Cast(@testint AS varchar(50))
GO

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
    With con
        .Provider = "sqloledb"
        .CursorLocation = adUseClient
        .Mode = adModeReadWrite
        .ConnectionString = "Server=MyServer;Database=MyDB;UID=MyUser;PWD=MyPassword;"
        .Open
    End With
    With com
        .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") & "'"
    End With


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,
Jeremy
0
Comment
Question by:Jeremy_D
  • 8
  • 4
  • 2
  • +1
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Does this work (can't test right now):

dim intParam as Variant
dim varParam as Variant

intParam = null
varParam = null

Set con = New ADODB.Connection
   Set com = New ADODB.Command
   With con
       .Provider = "sqloledb"
       .CursorLocation = adUseClient
       .Mode = adModeReadWrite
       .ConnectionString = "Server=MyServer;Database=MyDB;UID=MyUser;PWD=MyPassword;"
       .Open
   End With
   With com
       'i have this one generally as first one
       Set .ActiveConnection = con
       .Name = "spTestOutput"
       .CommandText = "spTestOutput"
       .CommandType = adCmdStoredProc
       'i am not sure about this one, i never used it meanwhile...
       .NamedParameters = True
       .Parameters.Append .CreateParameter("@testint", adInteger, adParamInputOutput, , intParam )
       .Parameters.Append .CreateParameter("@testvarchar", adVarChar, adParamInputOutput, 50, varParam)
       .Execute , , adExecuteNoRecords
 
       if IsNull(.Parameters("@testint").Value) then
         Debug.Print "@testint = NULL"
       else
         Debug.Print "@testint = " & CStr(.Parameters("@testint"))
       end if
       if IsNull(.Parameters("@testvarchar").Value) then
         Debug.Print "@testvarchar = NULL"
       else
         Debug.Print "@testvarchar = '" & .Parameters("@testvarchar") & "'"
   End With

hope it helps
Cheers
0
 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
Well, it 'works' in the sense that it calls the SP succesfully, but the default values in the SP definition are not used, instead the NULL is passed as a parameter value. Profiler shows something like this:

DECLARE @P1 int
DECLARE @P2 varchar(50)
SET @P1 = NULL
SET @P2 = NULL
EXEC spTestOutput @P1, @P2

This is the same thing that happens when I use my own code. So, no go.

Sidenote: the NamedParameters property is not really needed in this example. When set to True it means you can pass the parameters in a named fashion, skipping the ones you want to assume default values, in any order you want. When set to False you need to provide all parameters in the exact order as they are declared in the SP, and you may use different names from the ones used in the SP definition. Since I am providing all parameters in the correct order here, you can simply leave it out.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
As you want to define both parameters (because of the output), you have to give a value in ADO, there is no way around it.

BTW, your TSQL code is not 100% correct, because otherwise you woulnd't get the values back:

DECLARE @P1 int
DECLARE @P2 varchar(50)
SET @P1 = NULL
SET @P2 = NULL
EXEC spTestOutput @P1 OUTPUT, @P2 OUTPUT

So, to solve your problem, you have only 1 option as far as i can see: you cannot use the same parameter for input AND output at the same time...

CREATE PROCEDURE spTestOutput
@intParam int = NULL,
@varParam varchar(50) = NULL
@intValue int OUTPUT,
@varValue varchar(50) = OUTPUT
AS
IF @intParam IS NOT NULL
   SET @varValue = Cast(@intParam AS varchar(50))
IF @varParam IS NOT NULL
   SET @intValue = CAST(@varParam AS int)
GO

In that case, you can let away some of the parameters, but using the NamedParameters = True...

Cheers
0
 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
>> BTW, your TSQL code is not 100% correct, because otherwise you woulnd't get the values back:

You're right. I typed in the T-SQL OTTOMH. My profiling station is somehwere else...

>> So, to solve your problem, you have only 1 option as far as i can see: you cannot use the same parameter for input AND output at the same time...

Well, actually you can. It's no problem at all, either from T-SQL, or from VB code using with this parameter definition:

        .Parameters.Append .CreateParameter("@testint", adInteger, adParamInputOutput, , Null)
        .Parameters.Append .CreateParameter("@testvarchar", adVarChar, adParamInputOutput, 50, Null)

The only problem is the default values. If I declare the parameter as adParamInputOutput and do _not_ provide the default value (and neither set the .Value property of the parameter), there's an AV on the SQL Server and the process is terminated. It seems to me that ADO just can't handle Input/Output params *with default values*.

I guess I'll just have to make do with providing the defaults in my VB code...
0
 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
... or double up on the amount of parameters as you suggest...
0
 

Expert Comment

by:kamalnegi
Comment Utility


with cmd
    .commandType=adCmdText


    ' when passing an int use :
    .commandText="Exec spTestOutput @testint=" & value


    ' when passing a varchar use :
    .commandText="Exec spTestOutput @testvarchar='" & value & "'"
end with


and then,
execute the command object.

This ensures that no default values are hard compiled in ur VB code, Only problem is that CommandType is a 'adCmdText'

Cheers.
0
 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
kamalnegi: How do I get to my return value and output parameters with this method?
0
 

Expert Comment

by:kamalnegi
Comment Utility


Hi Jeremy..

    Modify ur stored procedure and let it return a record so that u can trap it in ur front end application.


Cheers..
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
kamalnegi: Thanks for the suggestion, but that does not answer my question. The Q is specifically about BI-directional parameters with SP default values.
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
There is one thing I see in the original code given above, and that is that you are not filling in the values for either one of the input side of the InputOutput parameters.  Your whole assumption is that one of the values will not be Null, so, if you pass a Null in the @testint parameter, then you'll try to do a Cast statement on the @testvarchar value which, based on your sample above, is also null.  That would cause your access violation.

   IF @testint IS NULL
       SET @testint = Cast(@testvarchar AS int)
   ELSE
       IF @testvarchar IS NULL
           SET @testvarchar = Cast(@testint AS varchar(50))

In your VB code:


       Set .ActiveConnection = con
       .Parameters.Append .CreateParameter("@testint", adInteger, adParamInputOutput, , Null)
       .Parameters.Append .CreateParameter("@testvarchar", adVarChar, adParamInputOutput, 50, Null)

' You need to set the values of one of the parameters here

       .Execute , , adExecuteNoRecords


Here is the syntax that is closer to what I use on the VB side:

Dim ADOPrm as ADODB.Parameter

      sParmName = "@testint"
      Set ADOPrm = com.CreateParameter(sParmName, adInteger, adParamOutput, ,Null)      
      com.Parameters.Append ADOPrm
      com.Parameters(sParmName).Value = 999
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Sorry, that should have been:

     Set ADOPrm = com.CreateParameter(sParmName, adInteger, adParamInputOutput, ,Null)      
0
 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
mdougan: You can always cast a NULL value to any basic datatype, that is not what's causing the AV. I can call the sp from QA with the following code with no problems:

declare @ti int, @tv varchar(50)
exec spTestOutput @ti OUTPUT, @tv OUTPUT
SELECT @ti, @tv
SET @ti = 10
exec spTestOutput @ti OUTPUT, @tv OUTPUT
SELECT @ti, @tv

This will give me the following result-sets:

------ ----------------------------------
NULL   NULL

------ ----------------------------------
10     10

Also, if, in the VB code, I explicitly put Null in the parameter's Value property (or let it default to Null with the ADO default parameter value), the code will run fine. There are only problems when I try to let the parameters default to the values given in the sp code, by omitting an ADO parameter default value and not putting an explicit value in the parameters Value property. For test purposes you could change the default value as given in the sp for @testint to 10 or so, in which case the same AV is caused. The fact that NULL is defined as a default for both values doesn't matter, you can choose any other value and the same problem occurs.

I even tried to provide a value for @testint from the VB side, just letting @testvarchar default to its sp-defined default value of NULL, like this:

.Parameters.Append .CreateParameter("@testint", adInteger, adParamInputOutput)
.Parameters.Append .CreateParameter("@testvarchar", adVarChar, adParamInputOutput, 50)
.Parameters("@testint").Value = 10
.Execute , , adExecuteNoRecords
       
This should obviously return the two parameters as 10 and '10', but again an AV is caused on the server.

What most annoys me with this is that I seem to be the first that notices this ADO shortcoming. With the popularity of VB/ADO/MSSQL you would expect this to be a 'know issue', but I haven't been able to find a "How to use T-SQL default values for ADO Input/Output parameters" KB article anywhere. This all just gives me the creeping feeling that *I* am the one doing something wrong - but what is the Right Way??? :(
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
OK, I think I have the answer for you.  The default value that you specify in the Stored Procedure parameter list will only be used if the parameter is not supplied in the procedure call AT ALL.

So, if your procedure looked like this:

CREATE PROCEDURE spTestOutput (
@testint int OUTPUT,
@testvarchar varchar(50) OUTPUT,
@inputtestint int = 30,
@inputtestvarchar varchar(50) = '20'
) AS
   IF @inputtestint = 10
       SET @testint = Cast(@inputtestvarchar AS int)
   ELSE
       IF @inputtestvarchar = '20'
           SET @testvarchar = Cast(@inputtestint AS varchar(50))
GO

And your Call to the proc looked like this:
declare @inti int, @ti int, @tv varchar(50)
set @inti = 10
exec spTestOutput @ti OUTPUT, @tv OUTPUT, @inti
SELECT @ti, @tv

Then, because you didn't pass the fourth parameter, the value for @inputtestvarchar will equal the default '20'.  In this example, because inputtestint was passed with a value of 10, you'll hit the first if condition, and the value of the output parameter @testint will be set to the default value in @inputtestvarchar ('20').

If you pass the fourth parameter, with a value of NULL, then this overrides the stored proc default, and in this case you'd get Null returned in @testint.

This is what is happening to you in VB.  In VB, you are trying to use the same parameter for input and output.  So, you always have to pass both parameters.  If you pass a default of NULL through ADO, or even if you leave the default off and don't fill in the value (something will get passed to the proc), then this will override the stored proc's defaults.  This is by design, what if you honestly want to pass a null to the proc to, say, update a field in the database with nulls...

To get this to work, you're going to have to define separate input and output parameters.  Then, you will always append the output parameters to the command object, but only append the one input parameter that you want to send.  Since you're using Named parameters, I think that SQL Server will be fine if the missing parameter is the third parameter and not the 4th, however I couldn't get the Query Analyzer to run it with syntax like:

exec spTestOutput @ti OUTPUT, @tv OUTPUT, ,@intv

Overall, I don't know if it's the answer that you want, but at least it explains the behavior that you're seeing.  

By the way, in the case where you don't set the value of the parm, and you also don't set a default, I would imagine that the parm gets passed to SQL Server with some strange value (whatever VB's empty evaluates to).  Maybe this is what's causing your AV
0
 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
mdougan: your story confirmes what I have encountered so far. If you don't provide ADO with a value or default, according to Profiler the stored proc gets called like this:

spTestOutput NULL, NULL
select @P1, @P2

Whereas _with_ an ADO value or default, it gets called like this (assume values 10 and '20'):

declare @P1 int
declare @P2 varchar(50)
set @P1 = 10
set @P2 = '20'
exec spTestOutput @P1 output, @P2 output
select @P1, @P2

The first code clearly shows that SQL Server is trying to return a couple of variables which aren't declared anywhere. Aside from that, they aren't even passed to the sp in the first place, which only gets two literal NULLs to chew on.

There is one thing I completely disagree with you on though:

> This is by design, what if you honestly want to
pass a null to the proc to, say, update a field in the database with nulls...

This behaviour is obviously a bug, not by design (I think the actual T-SQL going to the server clearly shows that - it's completely ineffective code, the select @P1, @P2 has no meaning at all without first declaring these variables). If I want to explicitly pass Null values to the stored proc, I can simply do so using the normal parameter definitions, there's no 'special case' needed for that:

cmd.Parameter("@testint").Value = Null

If I was trying to do something ADO wasn't designed for, It would give me an error (probably the dreaded 'Errors occurred' or some 8004005 error :( ). As it is now, it just sits there waiting for the parameters to return, only to find out that the process is terminated by the SQL Server because of the AV, resulting in a general network error.

I guess I'll have to accept that even ADO 2.6 isn't bug-free yet - but who expected that anyway?

I'll leave this open for a few more days. If no new ideas arise I'll give the points to AngelIII for first suggesting that I should use seperate parameters for input and output.
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Actually, I'd agree with you that this:

spTestOutput NULL, NULL
select @P1, @P2

Makes no sense, and is some sort of bug or "error or ommission".  What I was refering to as "by design" was the fact that SQL Server will take a Null passed in as an override to a default value for an input parameter.  However, given your example, there should be some special rules applied to output or input/output parameters.

Great!  Points to Angellll if you don't get any other ideas.  I hadn't read through the whole thread before posting about the use of separate input/output parms.

Good luck.
Mike
0
 
LVL 4

Author Comment

by:Jeremy_D
Comment Utility
Thanks everyone for your help.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now