can output params do this? - SQL Server Forums

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)

can output params do this?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-02-03 : 00:11:35

I have a stored procedure that brings back about 20 rows of about 15 columns. I must bring back a value of "MORERECORDS", which right now is beign brought back in a column.

I am using asp.net so this is why it is a problem. I am getting this value with a datareader, so advancing to get the value messes up that datareader positioning, and messes up my databind. Right now I am just temporarily using 2 datareaders, but this is killing my database server. Can I bring these two values back via an OUTPUT ? and then have a datareader as well? It seems if I can do this it would be alot better than having to use a dataset.

<code>
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
)
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,
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

</code>

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-02-03 : 00:24:03

Mike,

Because the "MORERECORDS" result is of type INT, why not use the in built RETURN mechanism?

As I mentioned in the other post the reader will have to be closed before you can access the RETURN value or any other OUTPUT parameter.

HTH

DavidM

"SQL-3 is an abomination.."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-02-03 : 01:29:07

byrmol thanks for the help

I havent figured out how to grab a return value in my asp.net code quite yet, but I have done it with a output parameter. Is the return value a better way to go ? (faster?) I definately want to do whatever it takes to do it the faster way. Pros? cons?

Thanks alot!

Mike123

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-02-03 : 02:05:45

Mike,

The RETURN parameter does not have to be declared as an argument in the Stored Procedure while an OUPUT parameter does.

The RETURN parameter is ONLY of type INT. The OUPUT parameter can be any type.

I consider it good practice to always declare the RETURN parameter in the middle tier. It will, by default, return 0 for success (even when you don't specify it in the Stored Procedure) and also return the Severity level of the last error in the Stored Procedure. Very handy...

In the .NET framework, the ParameterDirection enumeration reveals a value of "ReturnValue".

Here is some code (C#) to set up a RETURN value..


SqlParameter returnparameter = new SqlParameter("@RETURN_VALUE",System.Data.SqlDbType.Int);
returnparameter.Direction = ParameterDirection.ReturnValue;

Bind it to the Command object and you are away..

NB: The name of the parameter can be anything....

HTH

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources