title: 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
body: <p>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.</p>
  
  <p>Whenever I set my variable , 'output' (String), equal to the the output parameter of the stored procedure I get an ERROR 0. </p>
  
  <p>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.</p>
  
  <p>I have tried executing the stored procedure within SQL using set values.</p>
  
  <p>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 <strong>I think the issue lies within my stored procedure and not necessarily vba.</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>--Stored Procedure:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>' VBA CODE:</p>
  
  <pre><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: " &amp; output
  
      sqlconxn.Close
  
  End Sub
  </code></pre>
  
  <p>Debug log should just display the density value:</p>
  
  <p>EXAMPLE:</p>
  
  <p>0.163</p>