Hi guys,
I am trying to pass a list separated by comma to a SQL stored proc. Can anyone help me with some code example? Any help is appreciated.
Thx in adv.:)

Sankar Sengupta

__________________
Sankar Sengupta
Striving for the BEST

do u want code for SQL Stored Proc or code in ASP?

send a parameter that will have values seperated by comma.

In the SP, use a while loop and get the values using Substring menthod. Process it. Continue while loop till the values exhaust
If u can any clarifications on this , just post a reply

Balakumar V.

Hi there,

Here's some code from the archives of P2P, posted some time ago:

CREATE FUNCTION Inlist (@list varchar(8000))
RETURNS @tbl TABLE (val int not null) AS
BEGIN
        Declare @index int,
                @pos int,
                @str varchar(8000),
                @num int
        Set @pos = 1
        Set @index = 1
        While @index > 0
        Begin
                set @index = charindex(',', @list, @pos)
                if @index > 0
                        Set @str = substring(@list, @pos, @index - @pos)
                Else
                        Set @str = substring(@list, @pos, Len(@list))
                     Set @str = ltrim(rtrim(@str))
                Set @num = cast(@str as integer)
                Insert @tbl (val) values (@num)
                Set @pos = @index + 1
        End
        Return
 End
 --------------------------------
 Used in a select statement as followed:

"SELECT * FROM MyTable WHERE ID NOT IN (select val from inlist('1,2,3'))"

As this function returns a Table, you'll need at least SQL Server 2000 to use it.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Take a look at this one:
http://www.algonet.se/~sommar/arrays-in-sql.html

Cheers,
Frank

Thanks 2 u all guys. I've done it. Thanks again.

Sankar Sengupta