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

Need Cursor Help

Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is thatallthe rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number.

For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 ....

I could really use some help with this cursor:

Declare @.CustomerName as char (60),
@.seqno as int,
@.BaseSeqno as int
set @.Baseseqno = 278300

declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.CustomerName
while @.@.fetch_status=0
begin
set @.seqno = @.BaseSeqno + 1

update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.CustomerName

fetch next from c into @.CustomerName
end
close c
deallocate c

Make sure you are dealing with the right data. Is custnmbr same as customername?
Then, you would modify your sequence generation to

set @.seqno = @.seqno + 1

and set the @.seqno to 278300. You wouldnt need the @.Baseseqno at all. Based on your current logic, you will always gett he same number since you are adding 1 to @.BaseSeqno which is 278300.

Finally, I would recommend looking at alternatives to cursors. Read up books on line regarding cursors. There are pros/cons.|||

Thanks Dinakar for helping. I change my code to what is below and thought that I had things correctly from your explaination but the update still updates all the rows with 278301. Yes, customername is the same as custnmbr so I changed the code to reflect this.

Declare @.custnmbr as char (60),
@.seqno as int
set @.seqno = 278300

declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.custnmbr
while @.@.fetch_status=0
begin
set @.seqno = @.seqno + 1

update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.custnmbr

fetch next from c into @.custnmbr
end
close c
deallocate c
go

I'm a little confused with sequence generation. Isn't set @.seqno = @.segno + 1 in the correct location.

Thanks again for responding.

|||you might want to change the @.custnmbr from char to varchar. char is a fixed length data type. so it pads spaces if the string is less than the specified length. You might also want to add the condition adrscode='BILL TO' in the update statement, so if you have 2 customers with same name you have a higher chance the correct record can get updated.