0

I have the following stored procedure in SQL Server:

DECLARE @return_value int,
        @S int

EXEC    @return_value = [dbo].[SP_TestParams]
        @L1 = 2,
        @L2 = 2,
        @S = @S OUTPUT

SELECT  @S as N'@S'

SELECT  'Return Value' = @return_value

Procedure executes the code S = L1 + L2.
Return code returns the value depending of the S value:
IF L1 is null or L2 is null SET ret_code = 0
ELSE IF S < 10 SET ret_code = 1
ELSE IF S >= 10 SET ret_code = 2

I'm using the following the VBA code to trigger the above procedure, asking for parameters L1 and L2 (in given question let's assume that both are equal 2) and want to display the both @S and @return_value in the msgbox, but I get an error that procedure or function has too many arguments.

Sub TriggerProcedure ()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = "Driver={SQL Server};Server=MY_DATABASE;Uid=MY_LOGIN;Pwd=MY_PASSWORD;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
    cn.Open
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.SP_TestParams"
    cmd.ActiveConnection = cn
    
    Dim L1 As Integer, L2 As Integer, S As Integer, ret_code As Integer
    
    L1 = InputBox("Please enter parameter L1:")
    L2 = InputBox("Please enter parameter L2:")
    
    cmd.Parameters.Append cmd.CreateParameter("@L1", adInteger, adParamInput, , L1)
    cmd.Parameters.Append cmd.CreateParameter("@L2", adInteger, adParamInput, , L2)
    cmd.Parameters.Append cmd.CreateParameter("@S", adInteger, adParamOutput)
    cmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue)
    
    cmd.Execute
    S = cmd.Parameters("@S").Value
    return_code = cmd.Parameters("@return_value").Value
    ret_code = cmd.Parameters("@return_value").Value
    
    MsgBox "Parametr S - sum" & L1 & " i " & L2 & " equal to" & S & vbNewLine & "Return code of the procedure is " & ret_code
    
    cn.Close
End Sub

The VBA code should display the results of the two queries (S & return_code).

GSerg
  • 76,472
  • 17
  • 159
  • 346
Cezary Domański
  • 1
  • 1
  • "but receiving an error that procedure or function has too many arguments" - on which line does that show up? You have not included L1 and L2 in your procedure declaration - that might be the problem. – Tim Williams Apr 11 '23 at 20:15
  • Can you post the header for `[dbo].[SP_TestParams]` ? – Tim Williams Apr 11 '23 at 20:21
  • I could be wrong but I thought the return value had to be the first parameter in ADODB. – Dan Guzman Apr 11 '23 at 20:33
  • 3
    **Side note:** you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Apr 11 '23 at 20:34
  • I'm getting an error in line cmd.Execute. – Cezary Domański Apr 11 '23 at 21:14
  • 1
    Please *edit your question* to show the procedure there - we can't easily read it in a comment. – Tim Williams Apr 11 '23 at 21:23
  • I'm not really a SQL server user, but don't you have a CREATE PROCEDURE script for `dbo.SP_TestParams` ? That would be the most useful thing to show here... – Tim Williams Apr 11 '23 at 21:51
  • Hmm... doesn't `cmd.Execute` return an `ADODB.RecordSet` object? Don't you need to consume the record set and close it before you can retrieve the output parameters? e.g.: [Retrieve values in SQL Server Stored Procedures with ADO](https://learn.microsoft.com/en-us/troubleshoot/sql/connect/retrieve-values-stored-procedure) – AlwaysLearning Apr 12 '23 at 00:20
  • @AlwaysLearning You do, but all that happens automatically at `.Execute` due to the [deterministic destruction](https://stackoverflow.com/a/19038890/11683) of the returned recordset right at that line. Besides, it would not cause a "wrong number of arguments" error. – GSerg Apr 12 '23 at 09:30

0 Answers0