Returning a recordset AND an output parameter

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)

Returning a recordset AND an output parameter

Author

Topic

zippy
Yak Posting Veteran

69 Posts

Posted - 2001-10-24 : 20:09:26

Hi,

I have a sproc which should return an output paramater awell as a recordset.
the recordset is coming back fine but I have no output value coming though :(
here is the sproc:
CREATE PROCEDURE bbSelectTopic
@TopicID INT,
@Title VARCHAR(100) OUTPUT
AS
SELECT @Title = Title FROM bbTopics WHERE TopicID = @TopicID

SELECT bbMessages.UserID,gnUsers.ScreenName,bbProfiles.Title,bbProfiles.Signature,bbMessages.PostDate,bbMessages.Message
FROM bbMessages INNER JOIN
gnUsers ON gnUsers.UserID = bbMessages.UserID INNER JOIN
bbProfiles ON bbProfiles.UserID = gnUsers.UserID
WHERE TopicID = @TopicID
GO

When I execute it in query analyser the value Title is returned.

I think the problem must be in my asp,
this is what I am using:

Set cmd = Server.CreateObject("ADODB.Command")
with cmd
.ActiveConnection = cnAdmin
.CommandText = "bbSelectForums"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Parameters.Append .CreateParameter("ForumID",adInteger,adParamInput, , ForumID)
end with
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open cmd
if rs.EOF then
Results = 0
else
Results = 1
arrForums = rs.GetRows()
end if
Set rs = nothing
RemoveAllParameters(cmd)
with cmd
.ActiveConnection = cnAdmin
.CommandText = "bbSelectTopic"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Parameters.Append .CreateParameter("TopicID",adInteger,adParamInput, , TopicID)
.Parameters.Append .CreateParameter("Title",adVarchar,adParamOutput,100, Title)
end with
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open cmd
Title = cmd.Parameters("Title")
if rs.EOF then
Results = 0
else
Results = 1
arrTopics = rs.GetRows()
end if
Set rs = nothing
Set cmd = nothing

I am pretty sure that I have screwed that asp bit up,
I have tried what I though would work a number of different ways to no avail.

Please help me :)

Check out the worlds fastest computers at http://www.ocgurus.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources