Passing in large comma delimited string to sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Passing in large comma delimited string to sp

Author

Topic

calvin464
Starting Member

11 Posts

Posted - 2007-06-25 : 13:28:21

Hi,
I have problem with a passing in a comma delimited string that larger than 8000 characters. Does anyone know a better way to passing large size string than the way I did or do I pretty much have to split the text string into several smaller string for processing? Thanks

Here is my stored procedure:

CREATE PROCEDURE proc_UpdateGoodBatchTxn
@MerchantID VARCHAR(15)=NULL, @ListOfTxnId text=NULL,
@BatchIndex VARCHAR(20)=NULL, @BatchNum varchar(50)=NULL,
@UserName varchar(20)=NULL

AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION

DECLARE @OrderID varchar(10), @Pos int, @TxnID int
declare @TxnIdList varchar(8000)
select @TxnIdList = convert(varchar(8000), @ListOfTxnId)

SET @TxnIdList = LTRIM(RTRIM(@TxnIdList))+ ','
SET @Pos = CHARINDEX(',', @TxnIdList, 1)

IF REPLACE(@TxnIdList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@TxnIdList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
SET @TxnID = CAST(@OrderID AS int)
Update transactions Set TransactionStatus='Settled', SettlementDate=getdate() Where TransactionID= @TxnID
END
SET @TxnIdList = RIGHT(@TxnIdList, LEN(@TxnIdList) - @Pos)
SET @Pos = CHARINDEX(',', @TxnIdList, 1)
END
END
COMMIT TRANSACTION
GO

Kristen
Test

22859 Posts

Posted - 2007-06-25 : 13:32:56

There is a means of "splitting" a delimited string into a [temporary] table (including a method which will work for Text objects, IIRC) here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Once you have the individual items in a temp table you can just JOIN that to the table in your UPDATE statement

(Splitting the string should be much faster than the looping you are doing at present)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources