Text datatype with a stored procedure

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

SQL Server Development (2000)

Text datatype with a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-13 : 08:55:49

Mitch writes "You can't declare a local variable that is a text datatype in a stored procedure. You can't pass back a text variable with a stored procedure. The text datatype seems to be fairly limited as to what you can do with it.

I would *like* to write a stored procedure that returns a messageboard thread... the type of thread where a user has entered a message body. I'm completely stumped as to how I can get around the limitations associated with a text datatype.

During the course of my stored procedure, I would like to parse through the message body, perhaps strip out some HTML tags and do those types of things. The closest thing I can come up with is to cast the text field as a varchar(8000) and try to work with that - but that is no good. It's a rare occurance, but people DO sometimes post messages greater than 8000 characters.

I've scoured the Internet and posed this question to quite a few people and it seems that nobody has an answer. I'm beginning to to think that I'll forever be forced to execute the SQL statement and return an ADO recordset for this page.

Any ideas?

Thanks,
Mitch"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-13 : 08:55:49

Every time I've dealt with this I've had to return the TEXT data type in a record set. I do all the HTML parsing on the client (an ASP page). All the articles on this site come from text fields in a database -- as does this post you're reading.

MitchV
Starting Member

3 Posts

Posted - 2002-03-13 : 10:13:31

quote:

All the articles on this site come from text fields in a database -- as does this post you're reading.

No wonder it seems so inefficient! -- Just kidding :)

I know this takes it away from the SQL Server somewhat, but assuming there is no way for me to do this (use the data inside a text field outside of 8000 characters), what would be the best way to accomplish my goal. The query I return to generate my thread pages include the username, their profile selections, etc. So I'm doing a join between the thread table and the member table. Which runs faster than doing a select on the thread table and returning a new recordset for the member information for each thread record.

Essentially the tables are like this:

Thread Table:
threadid
subject
threadparent
username
threadbody
... other fields

Member Table:
Username
Registered Date
Number Of Posts
status
... additional information the user

The entire query I'd like to return is:

Select members.username,
members.status,
members.signupdate,
members.numberofposts,
members.emailviewable,
members.email,
subject,
locked,
threadid,
threads.datecreated,
parent,
threads.allowHTML,
threads.signature,
body
from Threads inner join members on threads.username = members.username where (startingthreadid=@threadid) AND (Deleted IS NULL) order by DateCreated

I *could* use a stored procedure to ONLY bring back a recordset the size of what is going to appear on the thread page (users have the option of how many posts to display on a given page) and do the parsing in the page - BUT I can't use a text with that either. ADO seems to be pretty flakey about using a field with a text datatype and when I return the above query from a stored procedure, I don't get the body field of the first record... no errors, it's just blank. Try returning an ADO recordset that includes TWO or more text variables and I bet you won't get one of them as well.

That leaves me with two choices I can think of. Build the above query in the page and return the ADO recordset and parse through the body (which is what I'm doing now)

or

Use a stored procedure to return everything BUT the body (text field) and as each record is read on the page, return a second recordset for each body.

Below is the stored procedure (no text parsing) I'd like to use so that I can only return the records I would like to appear on a given page (instead of returning a LOT or records and skipping over them on the ASP).

ALTER PROCEDURE "SP_DNThreadDisplay"

(
@Page int,
@RecsPerPage int,
@threadid int
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(

ID int IDENTITY,
username varchar(255),
status varchar(255),
signupdate datetime,
numberofposts int,
emailviewable varchar(50),
email varchar(255),
subject varchar(255),
locked varchar(50),
threadid float,
datecreated datetime,
parent float,
allowHTML varchar(50),
signature varchar(255),
body text

)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (username, status, signupdate, numberofposts, emailviewable,
email, subject, locked, threadid, datecreated, parent,
allowHTML, signature, body)
Select members.username,
members.status,
members.signupdate,
members.numberofposts,
members.emailviewable,
members.email,
subject,
locked,
threadid,
threads.datecreated,
parent,
threads.allowHTML,
threads.signature,
body
from Threads inner join members on threads.username = members.username where (startingthreadid=@threadid) AND (Deleted IS NULL OR Deleted <> 'YES') order by DateCreated

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks in advance for any insight or advice!

Mitch

MitchV
Starting Member

3 Posts

Posted - 2002-03-13 : 10:18:34

Sorry about the multiple post, it told me there was an error in my profile.

Mitch

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-13 : 11:56:56

Just make sure all your text files are the last ones in your SELECT list of fields and the last ones you reference in your code. That should work.

And I deleted the extra post.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources