05-29-2024 10:03 AM
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)
Solved! Go to Solution.
05-29-2024 11:28 AM - edited 05-29-2024 11:29 AM
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
05-29-2024 11:28 AM - edited 05-29-2024 11:29 AM
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