Passing parameters to stored procedure from ASP

Hi,
I've got a problem with passing a particular parameter from ASP to a SQL Server stored procedure. The problem is this;

ASP code generates a SQL statement;

lstrSQL = "GetMyData 5,4,'param', '1,2,3,4'

where '1,2,3,4' are ID's of some checkboxes. Obviously I cna't put them outside of '' because then that would extra parameters which is not what I want.

I then run the stored procedure. What I then have in my stored procedure is;

dbo.GetMyData
@id int,
@id2 int,
@mystring varchar(10),
@myinlist varchar(50)

SELECT * FROM data WHERE id IN(@myinlist)

but this doesn't work because @myinlist is '1,2,3,4' I can convert the id to a varchar to match;

SELECT * FROM data WHERE convert(Varchar(8), id) IN (@myinlist)

but that's no good because the inlist doesn't match up either
If I try and pass the string in as

'1','2','3','4' by using

lstrSQL = "GetMyData 5,4,'param', '''1'',''2'',''3'',''4'''

then that doens't work either.

Is there any simple way for me to do it ? Is it going to be easiest to build a SQL string and then execute the SQL inside the stored procedure ?

cheers
Andy

In my experience, the best way to pass parameters to a stored proc is with an ADO command. Use the CreateParameter and AppendParameter functions.

Code:

---

        Dim conADO As New ADODB.Connection()
        Dim cmdADO As New ADODB.Command()

        conADO.Open(GetConnectionString(), "web", "password")
        With cmdADO
            .ActiveConnection = conADO
            .CommandType = CommandTypeEnum.adCmdStoredProc
            .CommandTimeout = 90
            .CommandText = "myStoredProc"

            .Parameters.Append(.CreateParameter("@sName", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 40, sName))
        End With

---

Oops.. there is no AppendParameter function. Just append it to the collection. Also, GetConnectionString is defined elsewhere. You get the idea...

Sorry for the rambling...

To actually execute it, do

Set rsADO = cmdADO.Execute

Would that actually solve the problem though ? I think the problem is that when the parameter is passed into the stored procedure that it doesn't like me using IN() on a parameter because the commas are inside of the parameter rather than as a separator. (does that make sense ?)

cheers
Andy

Sorry, I didn't read your post thouroughly enough... :(

Just build the string in your stored proc

DECLARE @sSQL nvarchar(500)
SELECT @myList = '1,2,3,4,5,6'
SELECT @sSQL = N'SELECT * FROM sysObjects WHERE id IN (' + CONVERT(nvarchar(500), @myList) + ')'
exec sp_executesql @sSQL

Hi,
That's what I did in the end - I just wondered if I'd missed a trick in how to pass the values in, but the exec SQL works just fine (I'd done it before on SP's where I didn't know the table names because they were coming from a data dictionary, so I knew how to do it). Shame there's not arrays in SQL server !

Thanks again for your help

cheers
Andy