Passing a list of numbers to a stored procudure, having a size more than 8000 characters

Hi..

I m working on MS SQL Server 2000.

I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.

I was doing something like..

Create Procedure proc

(

@.Items varchar(100) List of numbers

)

AS Begin

Declare @.SQL varchar(8000)

Set @.SQL =

'

Select Query......

Where products IN (' + @.items + ') '

'

Exec (@.SQL)

This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."

Can any1 please help me out with this ASAP?

In 2000, you can split the list over mulitple parms and then concat them together when you call the sporc like so:

create proc whatever

( @.Items1 varchar(8000),

@.Itmes2 varchar(8000),

...

)

Declare @.SqlStart Varchar(8000),

@.SqlEnd varchar(8000)

set @.SqlStart = '

Select bla

where products in ('

set SqlEnd = ')'

exec (@.sqlStart + @.Items1 + @.Items2 + ... + @.SqlEnd)

That should work although it tedious to split the items on the calling end. Where do the itmes come from, can;t you use a join or something to get at the records you need?

Regards GJ

|||

Change the datatype from Varchar to Text & execute the query directly dont use any variable.

Code Snippet

Create Procedure [Proc]

(@.Items text)

AS

Begin

Exec ('Select Query......

Where products IN (' + @.items + ')')

End

|||

Thanks GJ...

Works fine after splitting the varchar...

I was thinking about using joins as well but I guess its not possible the way my query is.

Anyways... Thanks for ur help both of Us