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.