Passing parameters to stored procedure from ASP

[andypalomex]

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

[RoyceWindsor1]

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

        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

[RoyceWindsor1]

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

[RoyceWindsor1]

Sorry for the rambling...

To actually execute it, do

Set rsADO = cmdADO.Execute

[andypalomex]

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

[RoyceWindsor1]

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

[andypalomex]

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