VBA/SQL Server: How to return a succeed/error message back from db to vba excel

Question

I'm using stored procedures in my excel vba code to pass data to the sql server and send a return message back to the vba code. Well, I read a lot in other question topics on this and other sites, but I couldn't find the best solution for this approach. To give you a better imagination, I display an example:

VBA Code:

// defined vba function to return message from database to gui
Public Function fGetMessage() As String
    ' Declare variable and store the data id in the .Tag of userform
    Dim lngVarId As Long
    lngVarId = UserForm1.tbVar.Tag
    
    ' Instance of ADODB.Connection
    Dim oDatabase As clsDatabase
    Set oDatabase = New clsDatabase
    
    ' send sql statement with sproc to sql server and get a return message from database
    Dim oCmd As ADODB.Command
    Set oCmd = New ADODB.Command
    
    With oCmd
        .ActiveConnection = oDatabase.fGetDbConnection()
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.spDeleteProtocolData"
        .Parameters(1) = lngVarId
        
        Dim oRs As ADODB.Recordset
        Set oRs = New ADODB.Recordset
        Set oRs = oCmd.Execute
    End With
    
    fGetDeleteMessage = oCmd.Fields("ReturnMessage") // here I want the string return message
End Function

SQL-Server:

// defined stored procedure in sql server
CREATE PROCEDURE dbo.spDeleteProtocolData
   @VarId AS INT
AS
BEGIN
   SET NOCOUNT ON;

   BEGIN TRY
    BEGIN TRANSACTION
        UPDATE dbo.vProtocolData
        SET StatusId = 0
        WHERE VarId = @VarId
    COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
    -- store error message in log table
    INSERT INTO dbo.Logs (ErrNumber, ErrLine, ErrState, ErrSeverity, ErrSProc, ErrMessage, CreatedBy, CreatedAt)
    SELECT ERROR_NUMBER(), ERROR_LINE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_MESSAGE(), CURRENT_USER, GETDATE()

    ROLLBACK TRANSACTION
   END CATCH
END
GO

Does anyone have an idea or a better solution to handle return messages between vba excel and sql server database?

What string are you wanting to "return" here exactly? I don't see the SQL `SELECT`ing any data to return to the application, so where is the string coming from? — Thom A, Jun 10 '21 at 12:26
@Larnu I had the idea to take the "return value" of the sql server stored procedure. When you execute a stored procedure for example in ssms you see a result value called "return value". Maybe I could use this column "return value" to handle messages from the database. Is it a good approach or do you have a better solution? — yuro, Jun 10 '21 at 12:32
*"When you execute a stored procedure for example in ssms you see a result value called "return value""* No you don't. Are you, however, talking about the `RETURN` value, which denotes the success of the procedure? `0` for success, anything else for failure? — Thom A, Jun 10 '21 at 12:37
`RETURN` isn't a string, it's an `int`, so referencing to it as a "string" in your code is quite confusing. Does this answer your question? [Assign a stored procedure return value to a VBA variable](https://stackoverflow.com/questions/17868484/assign-a-stored-procedure-return-value-to-a-vba-variable) — Thom A, Jun 10 '21 at 12:47
As posted, your procedure **eats** the error. The application cannot know an actual error occurred and cannot use its own exception handling to process the error. Short answer is - don't eat the error, rethrow it. Do that and you can use standard exception handling in your application. And get rid of transaction in your procedure - a single statement is atomic - no need to wrap it in a transaction. — SMor, Jun 10 '21 at 13:26
If you want to get a message back from your stored procedure then add an OUT parameter which you can use for that - see eg. https://stackoverflow.com/questions/34145873/using-output-parameter-from-sql-server-stored-procedure-in-access — Tim Williams, Jun 10 '21 at 16:56

score 0 · Answer 1 · answered Jun 11 '21 at 19:23

You can use Output parameters:

Dim B As Boolean
Dim C As ADODB.Command
Dim I As Long
Dim P As ADODB.Parameter
Dim V As Variant

   Set C = New ADODB.Command
   C.ActiveConnection = glob_CONNECT
   C.CommandType = adCmdStoredProc
   C.CommandText = X.SQL_Name
   
   ' bind Inputparameter
   If X.NInParams > 0 Then
      For I = 1 To X.NInParams Step 1
         Set P = C.CreateParameter(X.InParam(I).SQL_Name, _
                                   X.InParam(I).ADODB_Type, _
                                   adParamInput, _
                                   X.InParam(I).SQL_Size, _
                                   X.InParam(I).SQL_Value)
         C.Parameters.Append P
      Next
   End If
   
   ' bind Outputparameter
   If X.NOutParams > 0 Then
      For I = 1 To X.NOutParams Step 1
         Set P = C.CreateParameter(X.OutParam(I).SQL_Name, _
                                   X.OutParam(I).ADODB_Type, _
                                   adParamOutput, _
                                   X.OutParam(I).SQL_Size)
         C.Parameters.Append P
      Next
   End If
   
   ' Execute SP (Errors are possible!)
   On Error GoTo 0
   On Error Resume Next
   C.Execute
   
   ' Error?
   If Err.Number <> 0 Then
      SQL_EXEC = False
      Exit Function
   End If

   ' Fetch Output
   If X.NOutParams > 0 Then
      For I = 1 To X.NOutParams Step 1
         V = VNormExec(C.Parameters(X.OutParam(I).SQL_Name), X.OutParam(I).SQL_Type)
         X.OutParam(I).SQL_Value = V
      Next
   End If

VBA/SQL Server: How to return a succeed/error message back from db to vba excel

1 Answers1