SQL Server/T-SQL/Data Type/text

READTEXT in a loop to read chunks of text.

 

5>
6> CREATE TABLE myTable
7> (
8>    pub_id         char(4)           NOT NULL,
9>    logo           image                 NULL,
10>    pr_info        text                  NULL
11> )
12> GO
7> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
8> SET TEXTSIZE 100    
9>                     
10> BEGIN TRAN
11>
12> DECLARE @mytextptr varbinary(16), @totalsize int,
13>     @lastread int, @readsize int
14>
15> SELECT
16>     @mytextptr=TEXTPTR(pr_info), @totalsize=DATALENGTH(pr_info),
17>     @lastread=0,
20>     @readsize=CASE WHEN (@@TEXTSIZE < DATALENGTH(pr_info)) THEN
21>         @@TEXTSIZE ELSE DATALENGTH(pr_info) END
22>      FROM myTable WHERE pub_id="1622"
23>
24> IF @mytextptr IS NOT NULL AND @readsize > 0
25>     WHILE (@lastread < @totalsize)
26>     BEGIN
27>         READTEXT myTable.pr_info @mytextptr @lastread @readsize
28>         IF (@@error <> 0)
29>             BREAK    
31>         SELECT @lastread=@lastread + @readsize
33>         IF ((@readsize + @lastread) > @totalsize)
34>             SELECT @readsize=@totalsize - @lastread
35>     END
36>
37> COMMIT TRAN
38> GO