469,342 Members | 5,590 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

How to specify DEFAULT for optional parameter to stored procedure wrapper function

My problem is this (apologies if this is a little long ... hang in there):

I can define a function in VB.NET with optional parameters that wraps a SQL procedure:

Expand|Select|Wrap|Line Numbers
  1.  Sub Test(Optional ByVal Arg1 As Integer = 0, _
  2.            Optional ByVal Arg2 As Integer = 0, _
  3.            Optional ByVal Arg3 As Integer = 0)
  5.     ' Call my SQL proc with the same signature
  7.   End Sub
And I can test for a missing argument by testing for the default I set:

Expand|Select|Wrap|Line Numbers
  1.  If (Arg1 = 0) Then
  2.       ' Arg1 is missing so leave it off the call to the stored proc
  3.     Else
  4.       ' Arg1 is provided so add it to the call to the stored proc
  5.     End If
Great, so in principle I can leave off unspecified optional parameters when calling the stored procedure and let the stored procedure default those parameters based on whatever defaults are specified in the stored procedure.

But hang on, what if my choice of default for the optional parameter is a valid value to send to the stored procedure? What if Arg1 can be 0 and that is different to the default set on the stored procedure?

Maybe I could use "Nothing" as my default for the optional parameter, and test using IsNothing. This works for complex types like String and any Objects. But fails for simple types like Integer, as setting an Integer to Nothing actually sets it to the value 0 and a subsequent call to IsNothing returns False:

Expand|Select|Wrap|Line Numbers
  1. Dim i As Integer
  2.     i = Nothing
  3.     If (IsNothing(i)) Then
  4.       ' this arm is never executed
  5.     Else
  6.       ' this will print 0
  7.       Debug.Print(i)
  8.     End If
Okay, so it looks like I need to replicate all the default values I have on the stored procedure into the wrapper VB.NET function. This is unfortunate as it duplicates code and leaves space for the two to become out of sync.

I want to be able to write something like:

Expand|Select|Wrap|Line Numbers
  1. Sub Test(Optional ByVal Arg1 As Integer = Default, _
  2.            Optional ByVal Arg2 As Integer = Default, _
  3.            Optional ByVal Arg3 As Integer = Default)
  5.     ' Call my SQL proc with the same signature
  7.   End Sub
And test for this special "Default" value on any type and not pass the stored proc parameter if this is set.

I have tried defining Structures that attempt to extend the base types (a bit like
those is System.Data.SqlTypes) but then I hit the problem that Optional parameters cannot be Structures (only classes or simple types).

I have also considered using Overloads rather than Optional parameters, but this doesn't work either as it seems you cannot replicate the same functionality you have with Optional parameters with Overloads. Considering my original function:

Expand|Select|Wrap|Line Numbers
  1. Sub Test(Optional ByVal Arg1 As Integer = 0, _
  2.            Optional ByVal Arg2 As Integer = 0, _
  3.            Optional ByVal Arg3 As Integer = 0)
I could re-write this using overloads as:

Expand|Select|Wrap|Line Numbers
  1. Sub Test(ByVal Arg1 As Integer, _
  2.            ByVal Arg2 As Integer, _
  3.            ByVal Arg3 As Integer)
  4.   End Sub
  6.   Sub Test(ByVal Arg1 As Integer, _
  7.            ByVal Arg2 As Integer)
  8.   End Sub
  10.   Sub Test(ByVal Arg1 As Integer)
  11.   End Sub
But I cannot do:

Expand|Select|Wrap|Line Numbers
  1. Sub Test(ByVal Arg2 As Integer, _
  2.            ByVal Arg3 As Integer)
  3.   End Sub
As this has the same signature as one of the previous versions. So although I can happily do this using optional parameters:

Expand|Select|Wrap|Line Numbers
  1. Test(Arg2:=3,Arg3:=1)
I cannot seem to achieve the same with an overload.

Can anyone think up a way of setting an optional parameter to some value I can detect as missing that is distinct from any default I may have set in the stored procedure that function wraps?

Sep 18 '09 #1
7 6703
7,872 Expert 4TB
Well, I don't know is VB supports it, but in C# I can do this:
Expand|Select|Wrap|Line Numbers
  1. int? i = null;
  2. if (i == null)
  3. {//this code fires
  4.    int b = 4;
  5. }
Adding a ? to the end of a value type allows it to be nullable. Maybe look to see if there is a VB equivilent to nullable value types?
Sep 18 '09 #2
3,525 Expert 2GB
If all your real world data is positive numbers then you could use a default of -1 to be your "null"

Microsoft does it all the time. Look at a listbox. The items in the listbox are indexed zero and up. If the selected index is -1 then nothing is selected.
Sep 18 '09 #3
3,525 Expert 2GB
TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.
Sep 18 '09 #4
Thanks for the suggestions,

I had considered the "special values" solution, but it feels like a kludge. You never know, one day date.MinValue might be a valid parameter value!

After much casting about I found a sort of solution (not as elegant as I had hoped but does the job) using generics.

I define a generic type that can accept Nothing even for simple intrinsic types (like integer) and then I can test IsNothing on all the inputs to see if they were specified. One nifty thing here is the Widening / Narrowing functions that allow the type to accept integers, strings, dates, etc. without an explicit Newing or type converting.

However I am not 100% happy with this solution, as to my mind "Nothing" means de-reference me. I wanted to use a constant like "DefaultValue", but I could not get the compiler to accept it no matter what I tried. So I ended up taking MS at face value:


... "Nothing - Represents the default value of any data type."

For those interested my code is:
Expand|Select|Wrap|Line Numbers
  1. '------------------------------------------------------------------------------
  2. ' Defaultable(Of dataType) Generic Class
  3. '
  4. ' Author: JC
  5. ' Created date: 21 Sep 2009
  6. ' Description: Generic class which enables simple intrinsic types to
  7. '  accept the Nothing so that IsNothing returns true
  8. '  Providing a means to detect default values for optional parameters
  9. '  Also enables all types to accept System.DB.Null values
  10. '------------------------------------------------------------------------------
  11. Public Class Defaultable(Of dataType)
  12.   Implements IComparable(Of dataType), _
  13.              System.Data.SqlTypes.INullable
  15.   '----------------------------------------------------------------------------
  16.   ' Member variables
  17.   '----------------------------------------------------------------------------
  18.   Private _Value As dataType
  19.   Private _bIsNull As Boolean
  21.   '----------------------------------------------------------------------------
  22.   ' New (Generic Overloaded)
  23.   '----------------------------------------------------------------------------
  24.   Public Sub New(ByVal newvalue As dataType)
  25.     _Value = newvalue
  26.   End Sub
  28.   '----------------------------------------------------------------------------
  29.   ' New (System.DBNull Overloaded)
  30.   '----------------------------------------------------------------------------
  31.   Public Sub New(ByVal newvalue As System.DBNull)
  32.     _bIsNull = True
  33.     _Value = Nothing
  34.   End Sub
  36.   '----------------------------------------------------------------------------
  37.   ' Value 
  38.   '----------------------------------------------------------------------------
  39.   Public Property Value() As dataType
  40.     Get
  41.       Return _Value
  42.     End Get
  43.     Set(ByVal newvalue As dataType)
  44.       _Value = newvalue
  45.     End Set
  46.   End Property
  48.   '----------------------------------------------------------------------------
  49.   ' Widening CType (Overloaded)
  50.   '----------------------------------------------------------------------------
  51.   Public Shared Widening Operator CType(ByVal d As dataType) As Defaultable(Of dataType)
  52.     Return New Defaultable(Of dataType)(d)
  53.   End Operator
  55.   '----------------------------------------------------------------------------
  56.   ' Widening CType (Overloaded)
  57.   '----------------------------------------------------------------------------
  58.   Public Shared Widening Operator CType(ByVal d As System.DBNull) As Defaultable(Of dataType)
  59.     Return New Defaultable(Of dataType)(d)
  60.   End Operator
  62.   '----------------------------------------------------------------------------
  63.   ' Narrowing CType
  64.   '----------------------------------------------------------------------------
  65.   Public Shared Narrowing Operator CType(ByVal d As Defaultable(Of dataType)) As dataType
  66.     Return d.Value
  67.   End Operator
  69.   '----------------------------------------------------------------------------
  70.   ' CompareTo
  71.   '----------------------------------------------------------------------------
  72.   Public Function CompareTo(ByVal other As dataType) As Integer Implements System.IComparable(Of dataType).CompareTo
  73.     If (TypeOf _Value Is IComparable(Of dataType)) Then
  74.       Return CType(_Value, IComparable(Of dataType)).CompareTo(other)
  75.     Else
  76.       Return _Value.GetType.ToString.CompareTo(other.GetType.ToString)
  77.     End If
  78.   End Function
  80.   '----------------------------------------------------------------------------
  81.   ' IsNull
  82.   '----------------------------------------------------------------------------
  83.   Public ReadOnly Property IsNull() As Boolean Implements System.Data.SqlTypes.INullable.IsNull
  84.     Get
  85.       Return _bIsNull
  86.     End Get
  87.   End Property
  89. End Class
I can now do this with my test function:
Expand|Select|Wrap|Line Numbers
  1.   Private Sub Test(Optional ByRef int1 As Defaultable(Of Integer) = Nothing, _
  2.                    Optional ByRef int2 As Defaultable(Of Integer) = Nothing, _
  3.                    Optional ByRef int3 As Defaultable(Of Integer) = Nothing)
  5.     Dim strCmd As String = "exec mystoredproc "
  7.     If (Not IsNothing(int1)) Then
  8.       strCmd &= "@int1=" & int1.Value.ToString & ", "
  9.     End If
  11.     If (Not IsNothing(int2)) Then
  12.       strCmd &= "@int2=" & int2.Value.ToString & ", "
  13.     End If
  15.     If (Not IsNothing(int3)) Then
  16.       strCmd &= "@int3=" & int3.Value.ToString & ", "
  17.     End If
  19.     Debug.Print(strCmd)
  21.   End Sub
Still I'd quite like to define my own Keyword "Default" that works like Nothing (i.e. causes no type conversion errors) but is detectable distinct from Nothing as I think MS have rather overloaded the meaning of Nothing here.
Sep 21 '09 #5
7,872 Expert 4TB
Wow VB makes it a long way to get the same as the ? nullable datatypes in C#
Sep 21 '09 #6
It is actually easy to define a Nullable type in VB just do:

Dim aVariable As Nullable(Of Integer)

And there you go, aVariable can now be set to Nothing (null) quite happily.

Problem: Nullable(Of T) is a Structure not a Class so you can't use it for optional parameters (not that those exist in c# anyway) and you can't test IsNothing on it you have to do .HasValue instead.

I need a type that can be used as an optional parameter hence the solution above.
Sep 22 '09 #7
7,872 Expert 4TB
Optional parameters exist in C#, using the params object[] notation I believe.
Although, I've not found a use for optional parameters in my own work yet.
Sep 22 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Jason | last post: by
1 post views Thread by ganesh wayachal via SQLMonster.com | last post: by
2 posts views Thread by The Plankmeister | last post: by
3 posts views Thread by Marco Segurini | last post: by
2 posts views Thread by bh | last post: by
4 posts views Thread by | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.