Skip to main content
C
ChrisR1ch
May 29, 2024
Solved

Calling DB Stored Procedure from OS with OUTPUT parameters

  • May 29, 2024
  • 1 reply
  • 0 views

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)

 

Best answer by RobbSalzmann

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

 

1 reply

R
RobbSalzmannAnswer
May 29, 2024

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

 

Terms & ConditionsAccessibility statement