output parameter from temp table

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

Transact-SQL (2000)

output parameter from temp table

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-02-03 : 18:40:49

I am just learning output parameters and have a bunch of sp's I have to go through. I am converting a stored procedure I have created so that 2 of the columns currently brought back in the dataset are returned outside of the dataset returned. (output parameters, or return values)

The two values are MoreRecords and ReplyCount, however they are not being returned properly. (They are both being returned as 0's)

If someone can lend a hand / critique my SP, that would be greatly appreciated.

Thanks alot!
mike123

alter 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
)
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, 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, 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,
forum_Pic,
R_MESSAGE
FROM #TempReply
WHERE ID > @FirstRec AND ID < @LastRec

SELECT @replyCount = (select count(reply_id) FROM forum_reply WHERE topic_ID = @topic_ID)
Return (SELECT COUNT(topic_ID) FROM #TempReply TR WHERE TR.ID > @LastRec )

SET NOCOUNT OFF

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-03 : 18:54:41

Why use a return value rather than another output parameter?

The return parameters will only be available after the recordset has been transferred. This is not a problem if you use a client based cursor, immediately disconnect the recordset and close the connection.

Try calling from query analyser to test if the code works correctly.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-02-03 : 20:07:37

nr, thanks for the help!

I am not sure why I am using a return value and an output parameter rather than two output parameters myself. I have switched the sp so that it uses both output parameters, however I am still having 0's returned as there value.

Is there something wrong with my code?

Here is another SPROC example, same situation.

Thanks again
MIke123

alter PROCEDURE select_searchUsers_TEST
(
@Page smallint,
@RecsPerPage tinyint,
@SQL VarChar(1000),
@moreRecords int OUTPUT
)
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 #TempSearchUsers
(
ID int IDENTITY,
userID int,
NameOnline varchar(15),
Points int,
Votes int,
userNote varchar(35),
GenderID tinyint,
date smalldatetime
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempSearchUsers (userID, NameOnline, Points, Votes, userNote, GenderID, date)
exec(@SQL)

-- 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
userID,
nameOnline,
points,
votes,
userNote,
genderID,
date
FROM #TempSearchUsers
WHERE ID > @FirstRec AND ID < @LastRec

SELECT @MoreRecords =
(
SELECT COUNT(userID)
FROM #TempSearchUsers SU
WHERE SU.ID >= @LastRec
)

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-03 : 23:54:22

Look ok.
What happens when you run it from query analyser

declare @i int
exec select_searchUsers_TEST 1, 10, 'select ...', @i out
select @i

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources