update column of a table with sequence numer padde

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 2008 Forums

Transact-SQL (2008)

update column of a table with sequence numer padde

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-08-02 : 06:28:21

I have to update column of a table with sequence numer padded with 0.
column length should be 31 character.
My current logic is getting time out.
Please help me in it

DECLARE @mStart int

--Initialize the sequence variable
SET @mStart = 0

--Declare a cursor to select rows from the table
DECLARE SeqCursor CURSOR LOCAL FOR
SELECT *
FROM [PieceBarcodeRecord]
DECLARE @curSeq VARCHAR(34)

--Open the cursor
OPEN SeqCursor

--Fetch from the cursor
FETCH SeqCursor

--Loop while fetch status is 0
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @mStart = @mStart + 1

SELECT @curSeq = ISNULL(REPLICATE('0', 31 - len(ISNULL(CONVERT(varchar(31), @mStart), 0))), '')
+ CONVERT(varchar(31), @mStart)

UPDATE [PieceBarcodeRecord]
SET [pbcBarcode] = @curSeq
WHERE CURRENT OF SeqCursor

FETCH NEXT FROM SeqCursor
END
--SELECT pbcBarcode FROM [PieceBarcodeRecord]

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-02 : 06:57:15

Since you are on SQL 2008 (or even SQL 2005) you should be able to use row_number() function in a set based query like this:

;WITH cte AS
(
	SELECT [pbcBarcode], 
		ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
	FROM
		[PieceBarcodeRecord]
)
UPDATE [PieceBarcodeRecord] SET
	[pbcBarcode] = RIGHT(REPLICATE('0',31) + CAST(RN AS VARCHAR(31)),31);

Replace that SELECT NULL with whatever ordering criterion you might actually want to use if you have such requirement.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-08-03 : 04:21:18

Thanks sunit

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources