ADO - cannot pass null to SP integer parameter

[Jon von der Heyden]

Hi

Having a heck of a time finding a way around this. Hope to get some help here.

Stored Proc in MSSQL:

CREATE PROC [Schema].[MyProcName]
 (
  @BusinessUnitID int
  ,@SomeVal1 nvarchar(50)
  , @SomeVal2 int
  , @SomeVal3 bit
  , @SomeVal4 bit
  , @SomeVal5 nvarchar(25)
 )
AS


DECLARE @HoldBusinessUnitID int


IF (@BusinessUnitID IS NULL)
 SELECT
  @HoldBusinessUnitID=MAX(BusinessUnitID) + 1
 FROM 
  [dbo].[BusinessUnits]
ELSE
  SET @HoldBusinessUnitID = @BusinessUnitID



-- rest of SP

Loading the BU ID variable:

varBUID = Evaluate(ThisWorkbook.Names(g_strSELECTED_BUSINESSUNIT).RefersTo)
Select Case varBUID
    Case Is = "": varBUID = Null
    Case Is > 0: varBUID = CLng(varBUID)
    Case Else: strError = "Invalid Business Unit ID": GoTo end_proc
End Select

Issue occurs where varBUID returns "" (as string). So load Null instead. I have also left it off entirely, i.e. leave it Empty.

BUID is one of many parameters. All parameters are loaded into an array called varParameters.

When I run the query:

Call .RunQuery(g_strINSERT_BUSINESSUNIT, varParameters)

RunQuery is part of my ADO class:

Public Function RunQuery(ByVal strQueryName As String, Optional ByRef varParms As Variant) As ADODB.Recordset
    Dim objRec      As ADODB.Recordset
    Dim lngParm     As Long


    With Me.Command
        .CommandText = strQueryName
        If .Parameters.Count > 0 Then
            On Error Resume Next
                For lngParm = .Parameters.Count - 1 To 0 Step -1
                    Call .Parameters.Delete(lngParm)
                Next lngParm
            On Error GoTo 0
        End If
        If IsMissing(varParms) Then
            Set objRec = .Execute(Options:=4)
        Else
            Set objRec = .Execute(Parameters:=varParms, Options:=4)
        End If
    End With


    Set RunQuery = objRec


    Set objRec = Nothing
End Function

I get the following Error:
-2147217913
Operand type clash: text is incompatible with int

I get that these are different data types, but as I might not have a BU ID, how can I handle the Null?

 

[Kyle123]

Hi Jon,

I'm confused, this is regular ol' SQL, it's just generating something like the below:

Exec KyleSP 123, 'Kyle', Null

Also, I don't have the same issue with your example, using:

Private Function Exec_sp(sStoredProcedure As String, ParamArray values() As Variant) As ADODB.Recordset

    Dim lcounter     As Long
    Dim vtemp        As Variant
    Dim sParameters  As String: sParameters = ""
    
    If UBound(values) <> -1 Then
        For lcounter = LBound(values) To UBound(values)
            vtemp = values(lcounter)
            Select Case VarType(vtemp)
                Case Is = vbString
                    values(lcounter) = "'" & Replace(vtemp, "'", "''") & "'"
                Case Is = vbNull
                    values(lcounter) = "Null"
            End Select
            sParameters = sParameters & values(lcounter) & ","
        Next lcounter
        sParameters = " " & Left(sParameters, Len(sParameters) - 1)
    End If
    
    Debug.Print "Exec " & sStoredProcedure & sParameters

    
End Function

This:

Sub test()
    Exec_sp "KyleSP", "Test 123", "Testing some quotes'es", Null
End Sub

Correctly outputs:

Exec KyleSP 'Test 123','Testing some quotes''es',Null

 

[bfreescott]

Jon, not sure if you've achieved the result you needed here, but I always use CStr to convert any variables I am passing to my ado queries to strings so they execute without error.

 

[Jon von der Heyden]

Hi All

I got Kyles method to work, thanks! Indeed I was using CStr (and correctly typing each parameter). The issue I had was that I have a string e.g. Test 123 and I failed to enclose in ''.

Kyle, by "regular ol' SQL" I mean calls like Select etc. I believe that the DB has some funky restriction that forbids me to pass SQL directly and only allows me to call SP's (so perhaps I need to ensure that this won't either fail if being used by a 'normal' user (I'm not sure if I have a different permissions set).

For now I'm cooking on gas again - thanks for all your help.

 

[Jon von der Heyden]

Whoops, it appears I have a different permissions set and the DB permissions forbid regular users from calling this.

I'm going to get the DB dude to handle ID -1 instead, so that way I won't need to pass NULL.

 

[Kyle123]

You could always build the parameters properly too

 

[Jon von der Heyden]

Yes I get that thanks Kyle (assuming you mean append them one-by-one and in doing so be more explicit). The class that contains the RunQuery method is used across a range of Excel applications now so I'm leaning towards a change in the SP. I know that the method I am using works if I conform to the parameter data types.