output param returning NULL value

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 param returning NULL value

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-05 : 17:38:00

I've just modified this stored procedure to use output params. The problem is my output parameter REPLYCOUNT is returning a NULL when there are no records, and I need it to return a 0 if it finds a null.
Also, the end result of this stored proc brings back a column MORERECORDS, but I do not need this column as I already have the value brought back as an OUTPUT param. I can't figure out how to not select this value. Any help greatly appreciated.

Thanks alot
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
begin
--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
set @MoreRecords =
(
SELECT COUNT(topic_ID)
FROM #TempReply TR
WHERE TR.ID > @LastRec
)
set @replycount=(select top 1 replycount from #TempReply
WHERE ID > @FirstRec AND ID < @LastRec )
SELECT ID,
userID,
nameOnline,
forum_Level,
forum_Posts,
REPLY_ID,
FORUM_ID,
R_AUTHOR,
TOPIC_ID,
R_STATUS,
R_DATE,
online,
--@replyCount
forum_Pic,
MoreRecords = @morerecords,
R_MESSAGE
FROM #TempReply
WHERE ID > @FirstRec AND ID < @LastRec
end

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-05 : 18:26:51

To get a 0 when the data is NULL, use ISNULL(ColumnName, 0). So I believe if you change it from set @replycount=(select top 1 replycount from #TempReply...to set @replycount=(select top 1 ISNULL(replycount, 0) from #TempReply...that it should work.

For MORERECORDS, just remove the column in the bottom select statement. So, instead of forum_Pic, MoreRecords = @morerecords,
R_MESSAGE...use forum_Pic, R_MESSAGE

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources