December 5, 2003 at 5:46 pm
I have a list of numbers seperated by commas. Is there are way to run a stored procedure like the one below. I know the SP is wrong, but you get the idea what I would like to do
theNums = 3,4,6,9
for x = 1 to theNums.length
UPDATE tempSalesDetail
SET BTI = 1
WHERE (tempSalesDetailID = theNums(x))
GO
Next
December 5, 2003 at 6:27 pm
Declare @varlist varchar(255)
Set @varList = ',1,2,3,5,' -- Commas before and after
Update tempSalesDetail
SET BTI = 1
WHERE CHARINDEX(',' + Convert(varchar, tempSalesDetailID) + ',', @varList) > 0
Note this is good for "small" tables,
for very large I would hardcode @varList
into the statement and run the query with sp_executesql dynamically
HTH
* Noel
December 5, 2003 at 7:00 pm
You could try something like:
declare @theNums varchar(1000) set @theNums = '3,4,6,9'
update tempSalesDetail
set SET BTI = 1
where ',' + @theNums + ',' like '%,' + convert(varchar, tempSalesDetailID) + ',%'
Cheers,
- Mark
Cheers,
- Mark
December 8, 2003 at 7:29 pm
I have a UDF that will do the parsing of the string and return a table. I then simply write something like the following.
UPDATE T
SET BTI = 1
FROM tempSalesDetail T
JOIN dbo.f_SplitString(@TheNums) n ON T.tempSalesDetailID = CONVERT(int, E.value)
Since the UDF f_SplitString creates a table with a column value varchar(255) I do a conversion on the join. You could certainly change it so that it has the integer type already.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply