April 2nd, 2005, 03:38 AM
|
|
Contributing User
|
|
Join Date: Feb 2005
Location: Near London
Posts: 112
Time spent in forums: 14 h 37 m 29 sec
Reputation Power: 5
|
|
I use this function I got from somewhere on the net, I know it's called SplitOrderIDs but it will work for any varchar like '37,38,39' etc
Code:
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
and here is an example of a procedure I use to update a table, using the function from above
Code:
CREATE PROC dbo.uspUpdatePJWithInvoiceNo
(
@NewInvoiceNumber int,
@OrderList varchar(500)
)
AS
UPDATE Personal_Journal
SET InvoiceNumber = @NewInvoiceNumber
WHERE [ID] IN (
SELECT * FROM dbo.SplitOrderIDs(@OrderList)
)
Hope this helps
Iain
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
