Need help constructing stored procedure

Hi,

I need to construct a stored procedure that will accept a set of comma seperated numbers. What I would like to do is something like this

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where dvd_detail_id in (@.contents)

dvd_detail_id is defined as int in the table.
The problem is if I declare @.contents as varchar, the procedure only recognizes the first number and ignores the others. Does anyone know how to get around this?

Thanks

Dan@.array is your @.contents...you need to pass the separator too..it can be a ',' or '-' etc

Create procedure shopping_cart (@.Array varchar(1000), @.separator char(1) ) AS
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
GO

hth|||I did the following on some other project

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where CharIndex(',' + Cast(dvd_detail_id as varchar(3)) + ',', @.contents) > 0

In the above case you should be sure that the values passed in are going to have , next to it. For eample, it should be ,1,2,3,

Does the above make sense?

AP