quote:

---

I am attempting to modify a stored procedure, I've outlined the changes that I've made in bold. (They aren't complete, and possibly incorrect). I basically just added the two output params I want to return, then I am attempting to set a value on teh first, but am lost on assigning the second value.

The way it is designed now it brings MORERECORDS and REPLYCOUNT as columns. I have upgraded my site to asp.net, so I want to bring these two values back as OUTPUT params, so I can just do a direct bind with the recordset when it is returned.

Any help / insight / suggestions really appreciated.

Thanks again guys!

Mike123


CREATE PROCEDURE select_thread_mod_TEST
(
@topic_ID int, -- Topic ID
@Page smallint, -- Which Page
@RecsPerPage tinyint, -- Number of Records to bring back
@userID int,
@replyCount int OUTPUT,
@moreRecords int OUTPUT
)
AS SET NOCOUNT ON
--Create temporary table to store records
CREATE TABLE #TempReply
(
ID int IDENTITY,
userID int,
nameOnline varchar(25),
forum_Level tinyint,
forum_Posts int,
REPLY_ID int,
FORUM_ID tinyint,
R_AUTHOR int,
TOPIC_ID int,
R_STATUS tinyint,
R_DATE datetime,
R_MESSAGE text,
online tinyint,
replyCount int,
forum_Pic tinyint
)
INSERT INTO #TempReply (userID, nameOnline, forum_Level, forum_Posts, REPLY_ID, FORUM_ID, R_AUTHOR, TOPIC_ID, R_STATUS, R_DATE, R_MESSAGE, online, replyCount, forum_Pic)
SELECT tblUserDetails.userID, tblUserDetails.nameOnline, tblUserDetails.forum_Level, tblUserDetails.forum_Posts, FORUM_REPLY.REPLY_ID, FORUM_REPLY.FORUM_ID, FORUM_REPLY.R_AUTHOR, FORUM_REPLY.TOPIC_ID, FORUM_REPLY.R_STATUS, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE,
case when exists (select userID from tblActive_Users where tblUserDetails.userid = tblActive_Users.userid) then
'1'
else
'0'
end as Online, (select count(reply_id) FROM forum_reply WHERE topic_ID = @topic_ID), tblUserDetails.forum_Pic

FROM tblUserDetails, FORUM_REPLY WHERE tblUserDetails.userID = FORUM_REPLY.R_AUTHOR AND TOPIC_ID = @topic_ID AND (FORUM_REPLY.R_STATUS < 3 OR R_AUTHOR = @userID) ORDER BY FORUM_REPLY.R_DATE asc
-- find out the last and first 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 indication of further records
SELECT ID,
userID,
nameOnline,
forum_Level,
forum_Posts,
REPLY_ID,
FORUM_ID,
R_AUTHOR,
TOPIC_ID,
R_STATUS,
R_DATE,
online,
@replyCount = replyCount,
forum_Pic,
MoreRecords =
(
SELECT COUNT(topic_ID)
FROM #TempReply TR
WHERE TR.ID > @LastRec
) ,
R_MESSAGE
FROM #TempReply
WHERE ID > @FirstRec AND ID < @LastRec
SET NOCOUNT OFF

GO



Edited by - mike123 on 03/04/2003 02:40:14

---