just wanted to share a little something that i found useful:I found myself using Dynamic SQL a lot to do updates on specific rows, like passing in a variable @UpdateIDs='12,534,23,632' to update those specific IDs. I wrote a function that doesn't require using Dynamic SQL.Instead of:exec('update table set column = 1 where ID in (' + @UpdateIDs + ')')I am now using:update table set column = 1where ID in (SELECT ID from fn_GetIDsFromString(@UpdateIDs))here's the function (and let me know if there's a better way to do it):CREATE FUNCTION fn_GetIDsFromString (@String varchar(8000))RETURNS @Return Table (ID int)AS BEGIN declare @Current smallint, @TempString varchar(20) set @Current = 1 while (CHARINDEX(',',@String,@Current) > 0) begin set @tempstring = CHARINDEX(',',@String,@Current) - @Current if @tempstring <> 0 begin insert into @Return (ID) values (substring(@String,@Current, cast(@tempstring as int)) ) end set @Current = CHARINDEX(',',@String,@Current) + 1 end if substring(@String,@Current, len(@String)) <> 0 insert into @Return (ID) values (substring(@String,@Current, len(@String)) ) return END 
Multiple Updates w/o having to use Dynamic SQL
Posted - 2002-09-18 : 15:24:40
