OK, I can now do it using the following code:


-------------

CREATE PROCEDURE spGetPagedContent

@id int,
@page int,
@numBytes int

AS

-- determine total size of text (in bytes)
DECLARE @totalBytes int
SELECT @totalBytes = DATALENGTH(content) FROM tblPageData WHERE [id]=@id;

--determine the number of bytes to display per page
DECLARE @numPages int
SELECT @numPages = ( @totalBytes / @numBytes )

DECLARE @bytesPerPage int
SELECT @bytesPerPage = (@totalBytes / @numPages)

-- determine offset
DECLARE @offset int
SELECT @offset = ((@page-1) * @numBytes)

IF @offset > (@totalBytes-@bytesPerPage)
SELECT NULL
ELSE

BEGIN

-- get pointer to page content
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR( [content] ) FROM tblPageData WHERE [id] = @id

-- now read text
READTEXT tblPageData.[content] @ptr @offset @numBytes
--SELECT @totalBytes
--SELECT @numPages
--SELECT @offset

END
GO

--------


HOWEVER!!! How do I return the number of pages AND the readtext value. I
need to do this to be able to determine when the last page is being
displayed!!

I have a client waiting on this functionality. Can anyone help???????
"Stephen McCormack" <stephenm@mwebsolutions.com.au> wrote in message
news:I68wa.4850$DP4.55811@news-server.bigpond.net.au...
> I have the following table (example only):
>
> CREATE TABLE [dbo].[tblHistory] (
> [id] [int] NOT NULL ,
> [content] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> This table stores a large biography on myself.
>
> Does anyone know if a stored procedure can be written to get say
> 10000characters at a time to simulate paging.
> More specifically the procedure would likely have the following skeleton:
>
> ---------------
>
> CREATE PROCEDURE spGetPagedText
>
> @page int, -- the page you wish to retrieve
> @numChars int -- the number of characters to retrieve perpage
>
> AS
>
> SET NOCOUNT ON
>
> -- return the appropriate text (i.e. if page 1 -> return len >0 and
> len<@numChars)
>
> SET NOCOUNT OFF
>
> GO
>
> ---------------
>
> This functionality would be extremely useful and save me al,ot of time.
> Thanks.
>
>

Stephen McCormack (stephenm@mwebsolutions.com.au) writes:
> HOWEVER!!! How do I return the number of pages AND the readtext value. I
> need to do this to be able to determine when the last page is being
> displayed!!

The easiest might be to return the number of pages in an output parameter.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp