empty recordset when multiple entires in parameter

[tinak]

Hi

I have a stored procedure in sql with parameter and vb in excel to pass all works when only one variable entered but I require user to be able to enter multiple codes.
I do not get an error but recordset is empty
Hope this makes sense this is my first post any suggestions greatly appreciated
Thank you

'This sub-routine defines an ADOBD command to return variables to VBA from SQL Stored Procedures.
     'The ADODB command executes a Stored Procedure on the SQL Server (cmd.CommandText = [Stored Procedure Name])
     'Input requirements from the Stored procedure are declared as variants at the start of the sub-routine.
Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim stCon As String 'SQL Connection string
    Dim stProcName As String 'Stored Procedure name
    ' Declare variables for Stored Procedure
    Dim myVariable As Variant
' Set ADODB requirements
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("n1:o1").ClearContents
    Range("A2").Select
' Open a Connection using an ODBC DSN named "Pubs".
    cnn.ConnectionString = "DSN=TINACUST;UID=ANALYST;PWD=ANALYST;"
    cnn.Open
' Defines the stored procedure commands
    stProcName = "fgbomprompt" 'Define name of Stored Procedure to execute.
    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = cnn 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run
' set parameters to be executed
    myVariable = InputBox("enter Stockcode")
Dim myvar2 As String
 myvar2 = Replace(Trim(myVariable), ", ", "', '")
 myvar2 = "'" & myvar2 & "'"
Debug.Print myvar2
    Set prm = cmd.CreateParameter("@code", adVarChar, adParamInput, 100, myvar2)
With cmd
    .Parameters.Append prm
    End With
' Execute stored procedure and return to a recordset
    rst.Open cmd.Execute
Sheet1.Range("A2").CopyFromRecordset rst
rst.Close
  Set rst = Nothing
if print myvar2
get '926315', '926316'

if enter 926315, 926316 in inbox box this is exactly how works in sql select query

[MarkNumskull]

I take it your SQL statement is something like SELCT 'your values' FROM 'Your Table' WHERE 'Field' = @code.

If this is the case, try changing the WHERE to WHERE 'Field' IN (@Code).

The IN clause takes comma separated values and returns all matching entries.