While loop issue - SQL Server Forums

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

Transact-SQL (2005)

While loop issue

Author

Topic

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2011-08-04 : 18:20:44

Hi everyone. I'm having some issues with a while loop I'm trying to run. I've included the portion of the SQL that is relevant. I'm trying to loop a record here which contains a very long description that I want to break out into chunks of 2000 characters that would be inserted in single rows. As you can see I'm using a cursor to loop through each record which contains a productID and the description. In my While loop statement, I'm determining whether to continue extracting based on the length of the remainder of the text that I'm extracting. The issue is that the loop is not looping and is only ran once. Does anyone see what the issue is here?

Open crs_Description
DECLARE @ProductID int
DECLARE @Description varchar(2000)
DECLARE @Seq int
DECLARE @StartIdx int
DECLARE @T1 int
DECLARE @T2 int
SET @StartIdx = 0
SET @T1 = 2000
SET @T2 = 2000
SET @Seq = 0

FETCH NEXT FROM crs_Description INTO @ProductID, @Description
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	
	WHILE (LEN(SUBSTRING(@Description, @StartIdx, @T1)) > 0)
	BEGIN
		--INSERT INTO STMT HERE
		SELECT @ProductID, 2, @Seq, 0, SUBSTRING(@Description, @StartIdx, @T1)
		
		SELECT @Seq = @Seq + 1

		SELECT @StartIdx = 1
		SELECT @StartIdx = @T1 + @StartIdx --ex. 2000 + 1 Range (2001 - 4000)
		SELECT @T1 = @T1 + @T2 --ex. 2000 + 2000 = 4000
	END
	
FETCH NEXT FROM crs_Description INTO @ProductID, @Description
END

CLOSE crs_Description

DEALLOCATE crs_Description
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-04 : 18:33:19

The @Description is of type varchar(2000) and you are trying to slice it into chunks of 2000 each. So there would be only one slice. Was @Description supposed to be really varchar(max) or something?

If you have only a few rows and a few thousand characters for each Description, it may not matter, but for larger data sets, it would be a lot more efficient to do this using a set-based query.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-04 : 18:39:59

why not use a set based solution to that


select	ProductID, Description = substring(Description, (v.number * 10) + 1, 10)
from	yourtable
	cross join master.dbo.spt_values v
where	v.type	= 'P'
and	v.number between 0 and 100
and	v.number	<= (len(prod_name) - 1) / 10

KH
[spoiler]Time is always against us[/spoiler]

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2011-08-04 : 18:41:56

D'oh! Right you are. The original field it originates from is actually of data type 'text'. But I can go ahead and use varchar(8000) since I'm technically using SQL Server 2000. Good eye and thanks beck!

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2011-08-04 : 18:42:34

quote:
Originally posted by khtan

why not use a set based solution to that

select ProductID, Description = substring(Description, (v.number * 10) + 1, 10)
from yourtable
cross join master.dbo.spt_values v
where v.type = 'P'
and v.number between 0 and 100
and v.number <= (len(prod_name) - 1) / 10

KH
[spoiler]Time is always against us[/spoiler]

I will look into this method. Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources