Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 680
  • Last Modified:

Bi-directional SP parameters with default values

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
Jeremy_D
Asked:
Jeremy_D
  • 8
  • 4
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Jeremy_DAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jeremy_DAuthor Commented:
>> 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
 
Jeremy_DAuthor Commented:
... or double up on the amount of parameters as you suggest...
0
 
kamalnegiCommented:


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
 
Jeremy_DAuthor Commented:
kamalnegi: How do I get to my return value and output parameters with this method?
0
 
kamalnegiCommented:


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
 
Jeremy_DAuthor Commented:
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
 
mdouganCommented:
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
 
mdouganCommented:
Sorry, that should have been:

     Set ADOPrm = com.CreateParameter(sParmName, adInteger, adParamInputOutput, ,Null)      
0
 
Jeremy_DAuthor Commented:
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
 
mdouganCommented:
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
 
Jeremy_DAuthor Commented:
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
 
mdouganCommented:
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
 
Jeremy_DAuthor Commented:
Thanks everyone for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now