Resolution/workaround - https://community.intersystems.com/post/sql-outbound-adapter-execute-pro...
Resolution - $$$SqlLongVarchar type of the input parameter was causing the issue as we need to pass the HL7 message as a %Stream.GlobalCharacter and LOB (larger object). But still not sure why it was affecting the output value. $$$SqlLongVarchar is the proper type to define the nvarchar(max) sql type.
I change the code as below for the input and input/output parameter; .tOutParams is returning the stored procedure output value as expected.
Set arrParam = 2
//Defining the input parameter
set arrParam(1)= pReq.RawContent
set arrParam(1,"SqlType") = 12
set arrParam(1,"CType") = $$$SqlWChar
set arrParam(1,"IOType") = 1
set arrParam(1,"Prec") = 8000
set arrParam(1,"SqlTypeName") = "TEXT"
//Defining the output parameter
set arrParam(2)=""
set arrParam(2,"SqlType") = $$$SqlVarchar
set arrParam(2,"IOType") = 2
set arrParam(2,"SqlTypeName") = "TEXT
//Stored procedure with the parameter definition
Set tQuery= "{call dbo.sp_InsertHL7(?,?)}"
Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"ib",.arrParam)
Thank you Jeffrey for the suggestion. As you said there is a max length limit for RawContent property. I modified the code as below;
Set tMessage = ##class(%Stream.GlobalCharacter).%New()
Set tHL7 = ##class(EnsLib.HL7.Message).%OpenId(pID)
Do tHL7.OutputToLibraryStream(.tMessage)