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 DBParamInfo object and ParameterDirection setting.  Can someone give guidance on the proper usage for OUTPUT.

Stored-Procedure

CREATE OR ALTER PROCEDURE [dbo].[TEST_OUTPUT_PARAM_PROC]
	(
		@OUT_PARAM_VALUE VARCHAR(100) OUTPUT
	)
	AS 
	BEGIN 
            --Do stuff that needs to be done here
	    SELECT @OUT_PARAM_VALUE = 'Param from Procedure';
	END												
GO

Extensibility Rule

'definition DB Call To stored procedure
Dim OutParamValue As String = ""
Dim tsSQL_CallProcwithOutputParamter As String = "DECLARE RC INT
                                                  EXECUTE RC = TEST_OUTPUT_PARAM_PROC @OUT_PARAM_VALUE OUTPUT"
Dim parameters As New List(Of DbParamInfo)
parameters.Add(New DbParamInfo("@OUT_PARAM_VALUE", OutParamValue,ParameterDirection.Output))  'paramDirection:
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
BRApi.Database.ExecuteSql(dbConnApp,tsSQL_CallProcwithOutputParamter.ToString,parameters,True)									
End Using
BRApi.ErrorLog.LogMessage(si,"OutParamValue = " & OutParamValue.ToString)

 

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