Store procedure help

[Johnny]

I am trying to loop through a string of ID's and check if the
productname for the ID is 'Chai' if so change it to 'Tea'. This is in
the northwind db - products table. call it exec sp_UpdateProduct
'1,2,3'
It does not work, any ideas - syntax, etc....

CREATE PROCEDURE dbo.sp_UpdateProduct

@IDs varchar(200)

AS

BEGIN

declare @sStr1 varchar(200)
declare @iStrLen1 int
declare @sParseElm1 char(2)
declare @sStatus varchar(200)
declare @count int

set @sStr1= @IDs
set @iStrLen1= len(@sStr1)
SET @count = 0
while charindex(',',@sStr1) <> 0
begin
select @sParseElm1 = substring(@sStr1,1,CHARINDEX(',',@sStr1 ) - 1)
set @sStatus = 'select productname from products where productid
='+@sParseElm1

if @sStatus = 'CHAI'
update products set productname = 'TEA'
else
update products set productname = 'CHAI'
set @sStr1 = substring(@sStr1, CHARINDEX(@sStr1,',') + 1, @iStrLen1)

end
end
GO

[Erland Sommarskog]

Johnny (jo*****************@yahoo.com) writes:

I am trying to loop through a string of ID's and check if the
productname for the ID is 'Chai' if so change it to 'Tea'. This is in
the northwind db - products table. call it exec sp_UpdateProduct
'1,2,3'
It does not work, any ideas - syntax, etc....

First, do not use the sp_ prefix for your stored procedures. This prefix
is reserved for system procedures, and SQL Server will first look for
such a procedure in the master database.

For a standard function for unpacking a list of integers to a table that
you then can join with, go to
http://www.algonet.se/~sommar/arrays...st-of-integers.

For for the replacement, you might be interested in the built-in
function replace(). Look it up in Books Online.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp