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?

 

[RoryA]

How are you populating varParameters?

 

[Jon von der Heyden]

Hi Rory. Thanks for noticing my thread.

The Excel sheet doesn't yield the parameters per the required data type so I have a variant for each.

Each variant is loaded per a Select Case (as exhibited above).

Finally varParameters is loaded as:

varParameters = VBA.Array(varBUID, varBusinessUnitName, varEtc)

The method works if I have a BUID (i.e. I'm editing an existing BU). But where a new one must be set-up, as there is no BU ID, Null is passed such that the SP will generate the ID.

 

[Kyle123]

I suspect that you'll either have to create the parameters properly or pass them as a SQL execute string to the command.execute method, something like:

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

    With Me.Command
        
        If Not IsMissing(varParms) Then
            For Each param In varparams
                sql = sql & IIf(param = Null, "Null", param) & ","
            Next param
            sql = Left(sql, Len(sql) - 1)
        End If
        
        Set objRec = .Execute("EXEC " & strQueryName & " " & sql)
        
    End With


    Set RunQuery = objRec
    
End Function

 

[Jon von der Heyden]

Hi Kyle

By create do you mean the ADO, i.e. Append parameters?

I can give that a go, thanks.

And is your other suggestion to have a nvarchar type parameter in the SP itself?

 

[Kyle123]

Yes, by properly, I mean append the parameters - it's trivial to set null that way
I've updated my earlier answer with the string example - though it's untested!

Obviously output params won't work unless created properly though - so no return params

 

[Kyle123]

Just had a dig, this is what I've used in the past:

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

    Dim ors          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
    
    
    
    Set ors = New ADODB.Recordset
    
    ors.Open "Exec " & sStoredProcedure & sParameters, p_oConnection, adOpenStatic
    
    Set Exec_sp = ors
    
End Function

 

[Jon von der Heyden]

Thanks for the follow-up Kyle, prior one had me in a pickle!

I see what you mean now, i.e. use Rec Open method. Thanks for the help - I'm getting close with this.

* Note sure why it won't accept a nvarchar value of: test 123 (but will play with the​ syntax a little)

 

[Jon von der Heyden]

I confess I've not played with this flavour before. Where I've used Rec Open I've always passed regular ol' SQL.

So with this syntax I have a final parameter to take nvarchar(25)

The value I am passing is: Test 123

I.e. a string that is alpha-numeric.

I have tried enclosing as 'Test 123'

Yet I get error: Incorrect syntax near 123. Is the space character causing me grief here? It works if I sub it for an underscore.

 

[Jon von der Heyden]

I tried to fudge sql variable in Immediate window and made a typo. It does work if I enclose in ''.

Thanks