OUTPUT OUTPUT OUTPUT - 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)

OUTPUT OUTPUT OUTPUT

Author

Topic

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-19 : 10:26:08

this has me screaming at the monitor...

CREATE PROCEDURE SP_GetPollResults
(
@intPollID int,
@intTotalVotes int OUTPUT
)
AS

BEGIN
SELECT @intTotalVotes = SUM(tbl_Polls_Answers.pollAnswerHits) FROM tbl_Polls_Answers
WHERE tbl_Polls_Answers.pollId = @intPollID
END

But it keeps asking for @intTotalVotes as an input!!! Why?!

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 10:33:03

because you need to give it a default value... try @intTotalVotes int = NULL OUTPUT for the parameter and it should be good...

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-19 : 10:38:10

Thanks, but even when I execute using the code below I don't get back the intTotalVotes:

objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "SP_GetPollResults"
objCmd.Parameters.Append objCmd.CreateParameter("intPollID", adInteger, adParamInput)
objCmd.Parameters("intPollID") = intPollId
objCmd.Parameters.Append objCmd.CreateParameter("intTotalVotes", adInteger, adParamOutput)

Response.Write objCmd("intTotalVotes")

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-19 : 10:51:03

Because you didn't execute the stored procedure:

objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "SP_GetPollResults"
objCmd.Parameters.Append objCmd.CreateParameter("intPollID", adInteger, adParamInput)
objCmd.Parameters("intPollID") = intPollId
objCmd.Parameters.Append objCmd.CreateParameter("intTotalVotes", adInteger, adParamOutput)
objCmd.Execute
Response.Write objCmd("intTotalVotes")

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-19 : 10:54:53

sorry should have posted all of it...

Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "SP_GetPollResults"
objCmd.Parameters.Append objCmd.CreateParameter("intPollID", adInteger, adParamInput)
objCmd.Parameters("intPollID") = intPollId
objCmd.Parameters.Append objCmd.CreateParameter("intTotalVotes", adInteger, adParamOutput)

Response.Write objCmd("intTotalVotes")

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.CursorType = adopenstatic
objRS.Open objCmd

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 11:05:43

have you tried assigned intTotalVotes a value in the either the ASP code and then executing and seeing if the value changes or have you tried just setting the value of intTotalVotes to any value inside the procedure after the select just to see if that value comes back?

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-19 : 11:22:39

yeah.. I set the intTotalVotes to 0 at the start of the page.. but when I display it.. it's blank..
..just out of interest.. how do I display the output from query analyser? ..i.e. I execute SP_GetPollResults 1 ..but how do I print @intTotalVotes

Edited by - thephoolish on 08/19/2002 11:26:52

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 11:27:54

I don't remember VBScript that well due to my new love c# but does objCmd("intTotalVotes") return the parameter or the value of the parameter or what ... i could just do a objCmd.Parameters.Item("intTotalVotes").Value ... just in case ... you should set the value to zero in the vbscript before you execute ... respone.write that value out to make sure it stuck then execute and then output it again ... humor me try that complete object names

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-19 : 11:32:17

objCmd("intTotalVotes") will return the value.. I've used this method before in SP and ASP, thats why I can't understand why this isn't working..
I tried objCmd.Parameters.Item("intTotalVotes").Value, but again it came up blank.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 11:34:12

you did try before and after the execution right? ... next try modifying your stored procedure ... comment out the select statement and put "set @intTotalVotes = 123"

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-19 : 11:50:40

Yes this returns the value..

I've checked the SQL for the procedure:

SELECT SUM(tbl_Polls_Answers.pollAnswerHits) FROM tbl_Polls_Answers
WHERE tbl_Polls_Answers.pollId = 1

and it returns the correct value..?!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-19 : 12:02:29

You need to return all the data from the recordset before the output parameters will be populated which shouldn't be a problem as you don't have a recordset.

There was a bug whereby if the parameter was accessed after being added to the collection then it would lose the return binding. It applied to any access - looking at it in debug, printing the before contents, changing a value, ...

see
http://mysite.freeserve.com/root/
Call stored procedures from ASP

For the way I (sometimes) call SPs from asp.

==========================================
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.

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-20 : 04:39:18

Still no joy..

OK.. here is the complete code listing..

Basically... I have 2 tables, 1 with poll questions and the other with poll answers....

ALTER PROCEDURE SP_GetPollResults
(
@intPollID int,
@intTotalVotes int = 0 OUTPUT
)
AS

BEGIN
SELECT tbl_Polls_Questions.pollId,
tbl_Polls_Questions.pollQuestion,
tbl_Polls_Answers.pollAnswerId,
tbl_Polls_Answers.pollAnswerText,
tbl_Polls_Answers.pollAnswerHits
FROM tbl_Polls_Questions
INNER JOIN tbl_Polls_Answers ON tbl_Polls_Questions.pollId = tbl_Polls_Answers.pollId
WHERE tbl_Polls_Questions.pollId = @intPollID
ORDER BY tbl_Polls_Answers.pollAnswerHits
END

BEGIN
SELECT @intTotalVotes = SUM(tbl_Polls_Answers.pollAnswerHits) FROM tbl_Polls_Answers
WHERE tbl_Polls_Answers.pollId = @intPollID
END

And the ASP code....

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn

Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "SP_GetPollResults"
objCmd.Parameters.Append objCmd.CreateParameter("intPollID", adInteger, adParamInput)
objCmd.Parameters("intPollID") = intPollId
objCmd.Parameters.Append objCmd.CreateParameter("intTotalVotes", adInteger, adParamOutput)

intVotes = 0

intVotes = objCmd("intTotalVotes")

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.CursorType = adopenstatic
objRS.Open objCmd

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-08-20 : 04:59:33

I dont think you've grasped nr's post about the order of reading information... deal with the recordset and then the parameter

objCmd.Parameters.Append objCmd.CreateParameter("intTotalVotes", adInteger, adParamOutput)

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.CursorType = adopenstatic
objRS.Open objCmd

'blah blah recordset stuff, if you need access to the recordset
'after you have used the parameter just dump to array
'arrayRecords = objRS.getRows()

objRS.close
set objRS = nothing

intVotes = 0
intVotes = objCmd.parameters("intTotalVotes")

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-20 : 06:13:27

Yup this works.. just a pity I have to dump the RS into an array.
Thanks everyone.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-20 : 08:11:06

what do you mean?! ...

quote:

Yup this works.. just a pity I have to dump the RS into an array.
Thanks everyone.

that is the BEST way to retrieve data used ADO ...

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-20 : 10:51:30

I'd have thought that having both the RS open and the array would have used up more system memory..

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-20 : 12:06:46

The RS is open anyway ... while you loop through the recordset using MoveNext you are putting locks on the database blocking data access (depending on your LockType and CursorType) the GetRows returns data that you can then manipulate or traverse in any way without worrying about being connected to the database ... while you are doing the MoveNext loop you are connected ... using GetRows dumps the data into memory and frees the connection for someone else to use...

of course with .NET the SqlDataReader is a wonderful advancement on the recordset. *these are just my opinions on what I have found to be true in past experiences; be a smart developer, make your own conclusions on your own experiences*

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-20 : 13:23:50

Np... some nice comments. ..I'm slowly making the move to ASP.NET

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources