My vba code is designed return a value from a stored procedure (in SQL) using an output parameter, but I am unable to get a value to return

Question

I am having trouble returning a value from an SQL table into a variable within vba. I am using dynamic SQL with a stored procedure that takes in 4 input parameters and one output parameter. After executing the stored procedure should return the density of a metal type which is passed in as a parameter. I am currently running the code using hard coded test values as parameters so I can expect the output. I do not believe the problem is with my connection or the way I am passing my parameters - but I'll show them anyway.

Whenever I set my variable , 'output' (String), equal to the the output parameter of the stored procedure I get an ERROR 0.

If there is something I am missing in my code let me know. Otherwise if there is a better way of returning an output parameter I can try that as well.

I have tried executing the stored procedure within SQL using set values.

The code below was generated automatically when I executed the stored procedure by right clicking it within SSMS (I wanted to see how it would get executed from vba) - because it is returning the wrong value I think the issue lies within my stored procedure and not necessarily vba.

USE [AFCD]
GO

DECLARE @return_value int,
        @record_value nvarchar(max)

EXEC    @return_value = [dbo].[GET_ADDITIVE_DATA]
        @user_index = N'Titanium(1)',
        @primary_col_index = N'DENSITY',
        @secondary_col_index = N'WIRE_TYPE',
        @data_table_name = N'WIRE_INDEX',
        @record_value = @record_value OUTPUT

SELECT  @record_value as N'@record_value'

SELECT  'Return Value' = @return_value

GO

The output of this query returns the @record_value parameter equal to 0.163 which is correct, but parameter @return_value is 0 which I don't understand.

--Stored Procedure:

USE AFCD
GO

ALTER PROCEDURE dbo.GET_ADDITIVE_DATA (@user_index nvarchar(max),
                                       @primary_col_index nvarchar(max),
                                       @secondary_col_index nvarchar(max), 
                                       @data_table_name nvarchar(max),
                                       @record_value nvarchar(max) output
                                       ) AS
BEGIN

    DECLARE @query nvarchar(max)

    SET @query = 'SELECT @record_value = ' + @primary_col_index  + 
             ' FROM ' + @data_table_name +
                 ' WHERE ' + @secondary_col_index + ' = ''' + @user_index + ''''

    EXEC sp_executesql @query, 
                   N'@record_value nvarchar(max) output', 
                   @record_value = @record_value output
END

' VBA CODE:

Private output As String
'-------------------------------------------------------------------------

' Parameters are passed in that will be used to create a SQL statement
' for a stored procedure.

Public Sub SQL_StoredProcedure(ByVal sql_ui As String, ByVal sql_pci As String, ByVal sql_sci As String, ByVal sql_dtn As String)

'
'
'
'
sqlcmd.ActiveConnection = sqlconxn ' makes the sql connection
    sqlcmd.CommandType = adCmdStoredProc ' sets command to a stored procedure
    sqlcmd.CommandText = "GET_ADDITIVE_DATA" ' name of the stored procedure

    ' Parameters that need to be called and defined whenever the stored procedure is called
    sqlcmd.Parameters.Append sqlcmd.CreateParameter("@user_index", adVarChar, adParamInput, 255)
    sqlcmd.Parameters.Append sqlcmd.CreateParameter("@primary_col_index", adVarChar, adParamInput, 255)
    sqlcmd.Parameters.Append sqlcmd.CreateParameter("@secondary_col_index", adVarChar, adParamInput, 255)
    sqlcmd.Parameters.Append sqlcmd.CreateParameter("@data_table_name", adVarChar, adParamInput, 255)
    sqlcmd.Parameters.Append sqlcmd.CreateParameter("@record_value", adVarChar, adParamOutput, 255)

    ' Parameter values are set equal to the values passed in through excel by the user
    ' The stored procedure in SQL opertaes using dynamic SQL so all inputs are defined as String values
    sqlcmd.Parameters("@user_index").Value = sql_ui
    sqlcmd.Parameters("@primary_col_index").Value = sql_pci
    sqlcmd.Parameters("@secondary_col_index").Value = sql_sci
    sqlcmd.Parameters("@data_table_name").Value = sql_dtn

    Set sqlrs = New ADODB.recordSet ' new recordset
    sqlrs.CursorType = adOpenStatic
    sqlrs.LockType = adLockOptimistic
    sqlrs.Open sqlcmd ' Execute the stored procedure

    output = sqlcmd.Parameters("@record_value").Value
    Debug.Print "OUTPUT: " & output

    sqlconxn.Close

End Sub

Debug log should just display the density value:

EXAMPLE:

0.163

Answer 1

I figured it out. For anyone who is curious whenever you are looking to return a single value from a database within SQL from vba don't use a recordset. (although I guess you can) The reason is Recordsets are designed to return data in the form of a table - which caused a lot problems for me.

To return a single value all you have to do is execute your SQL command in vba, and then set your return variable equal to the value of the output parameter from you're stored procedure. If that doesn't make sense I have put all my working code below from vba and my stored procedure.

TEST SUB - I used this to test one example of possible inputs from excel.

Private Sub TestSub()

SQL_StoredProcedure "Stainless Steel", "DENSITY", "WIRE_TYPE", "WIRE_INDEX"

End Sub

VBA MODULE

Option Explicit

Private output As String

' Parameters are passed in that will be used to create a SQL statement
' for a stored procedure.
Public Sub SQL_StoredProcedure(ByVal sql_ui As String, _
                                ByVal sql_pci As String, _
                                ByVal sql_sci As String, _
                                ByVal sql_dtn As String)

    On Error GoTo RunTimeError

    Dim sqlconxn As ADODB.connection ' Connection between vba and SQL
    Dim sqlcmd As ADODB.Command ' Operates as a command between SQL and vba
    Dim sqlfld As ADODB.field ' Used to refer to the records or fields in SQL

    Dim output As String ' The output retrieved from the SQL stored procedure
    Dim conxnString As String ' Connection string is used to make the connection between vba and SSMS

    Application.ScreenUpdating = False

    ' String used to establish a connection to the database
    conxnString = '{your connection string}'

    ' Every time the SQL_StoredProcedure function is called a new instance of the
    ' connection, command, and recordset are made and then immediately closed once the function finishes
    Set sqlconxn = New ADODB.connection
    Set sqlcmd = New ADODB.Command

    sqlconxn.ConnectionTimeout = 30
    sqlconxn.Open conxnString ' makes the connection between SQL

    MsgBox "Connection 1 state: " & GetState(sqlconxn.state) ' Checks the status of the connection

    sqlcmd.CommandType = adCmdStoredProc ' sets command to a stored procedure
    sqlcmd.CommandText = "GET_ADDITIVE_DATA" ' name of the stored procedure
    sqlcmd.ActiveConnection = sqlconxn ' makes the sql connection

    ' Parameters that need to be called and defined whenever the stored procedure is called
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@user_index", adVarChar, adParamInput, 255, sql_ui)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@primary_col_index", adVarChar, adParamInput, 255, sql_pci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@secondary_col_index", adVarChar, adParamInput, 255, sql_sci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@data_table_name", adVarChar, adParamInput, 255, sql_dtn)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@record_value", adVarChar, adParamOutput, 255)

    ' Executes the sql command with all parameters already passed in
    sqlcmd.Execute

    ' output string is set equal to the output parameter of the stored procedure
    output = sqlcmd.Parameters("@record_value").Value

    Debug.Print "OUTPUT: " & output ' prints whatever was returned from the SP

    sqlconxn.Close ' Closes the sqlconxn

    Exit Sub

RunTimeError: ' Reportd any errors that might occur in the system and

    Dim strError As String
    strError = "ERROR: " & Err.Number & vbCrLf & Err.Description
    MsgBox strError
    Debug.Print strError

Exit Sub

End Sub

CHECK STATE FUNCTION - this was just connection confirmation

Private Function GetState(state As Integer) As String

    Select Case state
        Case adStateClosed
            GetState = "Closed"
        Case adStateOpen
            GetState = "Open"

    End Select

End Function

SQL STORED PROCEDURE

USE AFCD
GO

ALTER PROCEDURE dbo.GET_ADDITIVE_DATA (@user_index nvarchar(max),
                                       @primary_col_index nvarchar(max),
                                       @secondary_col_index nvarchar(max), 
                                       @data_table_name nvarchar(max),
                                       @record_value nvarchar(max) output
                                       ) AS
BEGIN

    DECLARE @output_value nvarchar(max)
    DECLARE @query nvarchar(max)

    SET @query = 'SELECT @record_value = ' + @primary_col_index  + 
                 ' FROM ' + @data_table_name +
                 ' WHERE ' + @secondary_col_index + ' = ''' + @user_index + ''''

    EXEC sp_executesql @query, 
                       N'@record_value nvarchar(max) output', 
                       @record_value = @record_value output

END

OUTPUT

OUTPUT: 0.284