Listing 2: READTEXT with a Protected Text Pointer

BEGIN TRAN
DECLARE @mytextptr varbinary(16), @sizeneeded int, @pat_offset int
SELECT @mytextptr=TEXTPTR(pr_info),
    @pat_offset=PATINDEX('%Chicago%',pr_info) - 1,
    @sizeneeded=DATALENGTH(pr_info) -
        (PATINDEX('%Chicago%',pr_info) - 1)
    FROM pub_info (REPEATABLEREAD) WHERE pub_id='1622'
IF @mytextptr IS NOT NULL AND @pat_offset >= 0 AND
    @sizeneeded IS NOT NULL
    READTEXT  pub_info.pr_info @mytextptr @pat_offset @sizeneeded
COMMIT TRAN