Cursor and Procedure

**Erland Sommarskog** · Jun 27 '08, 07:06 PM

Re: Cursor and Procedure

Snow (cs180yong@gmai l.com) writes:

I want to find that the ntext column data string have more than 2000
characters. I need to truncate those strings to the segments with 200
character, then put those segments along with their table_name and
column_name to another table. Maybe need to use cursor? If so, how to
use it?
>
Your help is highly appreciated.

It's difficult to work with ntext columns more than one a time, so it
sounds like you need to use a cursor. But your description is far too
terse for me to want to give an example. Could you post:

1) CREATE TABLE statements for your table(s).
2) INSERT statements with sample data.
3) The desired result given the sample.

Since it's unpractical to post strings with 2000 characters, you
could pretent that the limit is 50 characters or whatever when you
compose the example.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Snow** · Jun 27 '08, 07:06 PM

Re: Cursor and Procedure

On May 18, 5:07 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

Snow (cs180y...@gmai l.com) writes:

I want to find that the ntext column data string have more than 2000
characters. I need to truncate those strings to the segments with 200
character, then put those segments along with their table_name and
column_name to another table. Maybe need to use cursor? If so, how to
use it?

>

Your help is highly appreciated.

>
It's difficult to work with ntext columns more than one a time, so it
sounds like you need to use a cursor. But your description is far too
terse for me to want to give an example. Could you post:
>
1) CREATE TABLE statements for your table(s).
2) INSERT statements with sample data.
3) The desired result given the sample.
>
Since it's unpractical to post strings with 2000 characters, you
could pretent that the limit is 50 characters or whatever when you
compose the example.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

Hello:

Thanks for the reply. Actually problem is ntext hold 4000 char. I
need to split ntext data into the segnments with 4000 char, and then
put those segnments to the another table. For example: The ntext data
string has 12000 char. It will be split to 3 segments. The another
table has the char field: long_seg. The column has segnment1(4000
char), segnment2(4000 char), segnment 3(4000 char) which comes from
the splitting ntext data sting with 12000 char. Another field:
segment_nbr holds segment number : 1, 2, and 3.

Here is the table to get the splitting segments.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tbl_longdata]') and OBJECTPROPERTY( id, N'IsUserTable') = 1)
drop table [dbo].[tbl_longdata]
GO

CREATE TABLE [dbo].[tbl_longdata] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[table_name] [char] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[col_name] [char] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[tableId] [int] NULL ,
[segment_nbr] [int] NULL ,
[long_seg] [nvarchar] (4000) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

Here is the simple cursor test one table: tbl_deptTri.

DECLARE @id_cur int
DECLARE @deptTriDes ntext
Declare @segment_nbr_cu r int
DECLARE @segment_nbr_ne w INT
DECLARE @deptTriDes_cur ntext
DECLARE @stringpos INT

DECLARE TableCursor CURSOR FOR
SELECT [id], [deptTriDes]
FROM tbl_deptTri

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @id_cur, @deptTriDes_cur

WHILE @@FETCH_STATUS = 0
BEGIN
SET @segment_nbr_ne w = 0
SET @segment_nbr_cu r = CEILING(DATALEN GTH(@deptTriDes _cur)/8000)
WHILE @segment_nbr_ne w <= @segment_nbr_cu r
BEGIN
SET @segment_nbr_ne w = @segment_nbr_ne w + 1
SET @stringpos = (@segment_nbr_n ew - 1)*4000 + 1
INSERT INTO tbl_LongData ([table_name],[col_name],[tableID],
[segment_nbr],[long_seg])
VALUES (
'tbl_deptTri',
'deptTriDes',
@id_cur,
@segment_nbr_ne w,
SUBSTRING(@dept TriDes_cur,@str ingpos,4000)
)

END
FETCH NEXT FROM TableCursor INTO @id_cur, @deptTriDes_cur
END
CLOSE TableCursor
DEALLOCATE TableCursor

However, I got the error: The text, ntext, and image data types are
invalid for local variables.

what am I missing here?

Thanks

**Erland Sommarskog** · Jun 27 '08, 07:06 PM

Re: Cursor and Procedure

Snow (cs180yong@gmai l.com) writes:

However, I got the error: The text, ntext, and image data types are
invalid for local variables.
>
what am I missing here?

Exactly what it says. I will have to look into this tonight to see if
I can think of something. But the in my while, maybe you can answer one
question that I forgot: which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Erland Sommarskog** · Jun 27 '08, 07:06 PM

Re: Cursor and Procedure

Snow (cs180yong@gmai l.com) writes:

However, I got the error: The text, ntext, and image data types are
invalid for local variables.

OK, assuming that you are stuck on SQL 2000 and cannot move to the new
MAX data types, the solution would be always read the column from the
table, as in this example (which is untested, as I did not have the
source table, nor any test data):

DECLARE @id_cur int
DECLARE @deptTriDes ntext
DECLARE @segment_nbr_ne w INT
DECLARE @stringpos INT

DECLARE TableCursor CURSOR FOR
SELECT [id], [deptTriDes]
FROM tbl_deptTri

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @id_cur, @deptTriDes_cur

WHILE @@FETCH_STATUS = 0
BEGIN
SET @segment_nbr_ne w = 0

SELECT @segment_nbr_cu r = CEILING(DATALEN GTH(deptTriDes)/8000)
FROM tbl_deptTri
WHERE id = @id_cur

WHILE @segment_nbr_ne w <= @segment_nbr_cu r
BEGIN
SET @segment_nbr_ne w = @segment_nbr_ne w + 1
SET @stringpos = (@segment_nbr_n ew - 1)*4000 + 1
INSERT INTO tbl_LongData ([table_name],[col_name],[tableID],
[segment_nbr],[long_seg])
SELECT
'tbl_deptTri',
'deptTriDes',
@id_cur,
@segment_nbr_ne w,
SUBSTRING(deptT riDes, @stringpos, 4000)
FROM tbl_deptTri
WHERE id = @id_cur

END
FETCH NEXT FROM TableCursor INTO @id_cur
END
CLOSE TableCursor
DEALLOCATE TableCursor

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Cursor and Procedure

Cursor and Procedure

Comment

Comment

Comment

Comment