update all rows where id isin comma delimted string 3

[Tokhra]

Hi all,

I need to create a sproc that will take:
@ids varchar(5000),
@value varchar(50)

and want it to do something like:

update table
set
value = @value
where id isin(@ids)

@ids is a comma delimited string e.g. '123, 123, 123, 123'. I don't think ISIN is actually a function, but I used to to explain what im trying to accomplish. I tried breaking the id's up and creating a temp table, but had some problems with that for various reasons.

Any help would be appreciated. Thanks, Matt.

 

[JamesLean]

Have a look at this FAQ:

Passing a list of values to a Stored Procedure
faq183-3979

--James

 

[Glyndwr]

Tokhra,

there is a freely available function called fn_split, I can't remember where i got it from - either Andrew Novick's site, sqlteam or microsoft.(shame on me)

which really makes this stuff easy ( create function included

drop table #t
go
create table #t(val1 int, val2 varchar(40))
insert into #t values(32,'aaaadsf')
insert into #t values(23,'bbdsf')
insert into #t values(3,'ccdsf')
insert into #t values(32,'dddsf')
insert into #t values(32,'deesf')
go

update #t set #t.val2 = #t.val2 + ' - ' +cast(val1 as varchar(10))
from (select * from master.dbo.fn_split('32,23,29,23,3',',')) list 
	where  list.value = #t.val1

This is the create function code

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  User Defined Function dbo.fn_Split    Script Date: 30/10/2003 14:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_Split]
GO

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
	@value varchar(8000),
	@bcontinue bit,
	@iStrike smallint,
	@iDelimlength tinyint

IF @sDelim = 'Space'
	BEGIN
	SET @sDelim = ' '
	END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
	BEGIN
	WHILE @bcontinue = 1
		BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
		IF CHARINDEX(@sDelim, @sText)>0
			BEGIN
			SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			
--Trim the element and its delimiter from the front of the string.
			--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
			SET @idx = @idx + 1
			SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
		
			END
		ELSE
			BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			--Exit the WHILE loop.
SET @bcontinue = 0
			END
		END
	END
ELSE
	BEGIN
	WHILE @bcontinue=1
		BEGIN
		--If the delimiter is an empty string, check for remaining text
		--instead of a delimiter. Insert the first character into the
		--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
		IF DATALENGTH(@sText)>1
			BEGIN
			SET @value = SUBSTRING(@sText,1,1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			SET @idx = @idx+1
			SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
			
			END
		ELSE
			BEGIN
			--One character remains.
			--Insert the character, and exit the WHILE loop.
			INSERT @retArray (idx, value)
			VALUES (@idx, @sText)
			SET @bcontinue = 0	
			END
	END

END

RETURN
END






GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Glyndwr

 

[smnetto]

Common guys, is easier by this way:

update table ... set value=@value where id is in (replace(@ids,char(39),''))


hope i help u...

Sergio
MCP SQL Server

 

[JamesLean]

Sergio,
It may look easier but your method will NOT work.

--James

 

[Tokhra]

Thanks to everyone that replied, I used this in the end:

DECLARE @list varchar(5000)

SET @list = '353, 354, 355, 356, 357, 358, 359'

DECLARE @pos int

--create table to hold parsed values
CREATE TABLE #list (ProductID int)

--add comma to end of list
SET @list = @list + ','

--loop through list
WHILE CHARINDEX(',', @list) > 0
BEGIN
--get next comma position
SET @pos = CHARINDEX(',', @list)

--insert next value into table
INSERT #list VALUES (LTRIM(RTRIM(LEFT(@list, @pos - 1))))

--delete inserted value from list
SET @list = STUFF(@list, 1, @pos, '')
END

UPDATE COMS_Products
SET ShootRef = 485
WHERE ProductID IN (SELECT ProductID From #list)

DROP TABLE #list

With some help from the FAQ JamesLean pointed out

Thanks again everyone

 

[nigelrivett]

UPDATE COMS_Products
SET ShootRef = 485
WHERE ',' + @list + ',' like '%,' + convert(varchar(20),ProductID) + ',%'

This will of course not use any indexes so will be slow on large tables but is very simple.

don't know what the split function above is doing - suspect it is catering for more conditions but a simpler verion is

create function s_ParseCSVString
(
@CSVString varchar(8000) ,
@Delimiter varchar(10)
)
returns @tbl table (s varchar(1000))
as
/*
select * from dbo.s_ParseCSVString ('qwe,c,rew,c,wer', ',c,')
*/
begin
declare @i int ,
@j int
select @i = 1
while @i < len(@CSVString)
begin
select @j = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
select @j = len(@CSVString) + 1
end
insert @tbl select substring(@CSVString, @i, @j - @i)
select @i = @j + len(@Delimiter)
end
return
end


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

The other common method for this is to use dynamic sql.
Gets round the index problem but has security issues.

declare @sql varchar(8000)
select @sql = 'update Products SET ShootRef = 485 WHERE ProductID IN (' + @list + ')'
exec (@sql)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.