Forum Discussion

ChrisR1ch's avatar
ChrisR1ch
New Contributor III
7 months ago

Calling DB Stored Procedure from OS with OUTPUT parameters

I am trying to call a database stored procedure from an extensibility rule.  The stored procedure returns an OUTPUT parameter, but I'm unsure how to reference that OUTPUT parameter, using the DBParam...
Extenders
  • RobbSalzmann's avatar
    RobbSalzmann
    7 months ago

    You'll need to use the DataTable object returned by BRApi.Database.ExecuteSql:

    Dim OutParamValue As String = ""
Dim tsSQL_CallProcwithOutputParamter As String = "DECLARE  INT; EXECUTE  = TEST_OUTPUT_PARAM_PROC @OUT_PARAM_VALUE OUTPUT; SELECT @OUT_PARAM_VALUE AS OUT_PARAM_VALUE;"
Dim parameters As New List(Of DbParamInfo)
parameters.Add(New DbParamInfo("@OUT_PARAM_VALUE", OutParamValue, ParameterDirection.Output))

Dim dt As DataTable = Nothing
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    dt = BRApi.Database.ExecuteSql(dbConnApp, tsSQL_CallProcwithOutputParamter, parameters, True)
End Using

If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
    OutParamValue = dt.Rows(0)("OUT_PARAM_VALUE").ToString()
    BRApi.ErrorLog.LogMessage(si, "OutParamValue = " & OutParamValue)
Else
    BRApi.ErrorLog.LogMessage(si, "Output parameter not found or DataTable is empty.")
End If

     

RobbSalzmann's avatar
RobbSalzmann
Valued Contributor II
7 months ago

You'll need to use the DataTable object returned by BRApi.Database.ExecuteSql:

Dim OutParamValue As String = ""
Dim tsSQL_CallProcwithOutputParamter As String = "DECLARE  INT; EXECUTE  = TEST_OUTPUT_PARAM_PROC @OUT_PARAM_VALUE OUTPUT; SELECT @OUT_PARAM_VALUE AS OUT_PARAM_VALUE;"
Dim parameters As New List(Of DbParamInfo)
parameters.Add(New DbParamInfo("@OUT_PARAM_VALUE", OutParamValue, ParameterDirection.Output))

Dim dt As DataTable = Nothing
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    dt = BRApi.Database.ExecuteSql(dbConnApp, tsSQL_CallProcwithOutputParamter, parameters, True)
End Using

If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
    OutParamValue = dt.Rows(0)("OUT_PARAM_VALUE").ToString()
    BRApi.ErrorLog.LogMessage(si, "OutParamValue = " & OutParamValue)
Else
    BRApi.ErrorLog.LogMessage(si, "Output parameter not found or DataTable is empty.")
End If