4

I am using ASP classic with ADO, connecting to SQL Server 2008.

I inherited this code and it is so mangled that I will try to recreate the relevant parts. If you need more detail or I left something out, please let me know.

I create a command and add parameters

oCmd.CommandType = adCmdStoredProc
...
oCmd.Parameters.Append oCmd.CreateParameter("@MyOutputParam", adInteger, adParamOutput, 4, NULL)

Later, I open a reader from that command:

oRS.Open oCmd, , adOpenForwardOnly, adLockReadOnly

After that, while oRS is open but before I've read any records or values, I try to get the output parameter's value using one of the lines below:

val1 = oCmd("@MyOutputParam")
val2 = oCmd("@MyOutputParam").Value
val3 = oCmd.Parameters("@MyOutputParam").Value

All three (val1, val2, val3) variables are DB NULL.

I have confirmed that running the SP in query analyzer returns a value to the @MyOutputParam parameter:

declare @p33 int
exec usp_GetResultAndOutput 1, 2, 3, @p33 output
select @p33

That returns a recordset of my expected records and a second recordset showing a number in a single row.

I've even tried calling rs.NextRecordset before attempting to get the output parameter and that didn't work.

Is there some other way that I need to be handling Output parameters?

Is it okay that I am returning a recordset and output parameters?

slolife
  • 19,520
  • 20
  • 78
  • 121

2 Answers2

5

Output parameters cannot be retrieved until all the recordsets are enumerated until the end. Just think, how could the client possibly retrieve the output value of a parameter before the execution finishes? The Execute() call is simply starting the execution, the batch continues to execute on the server until all results are returned. While a client is iterating over a resultset produced by a SELECT the batch is executing that SELECT. The value of the output parameter is known only at the end of the batch. Therefore is not possible to know the output parameter value until the batch finished, which implies that all statements have executed, which in turn requires that all resultsets were consumed (iterated) by the client.

This is the canonical form of parsing a set of results:

do
{
  while (rs.MoveNext)
  {
    // do something with the row
  }
} while (Not rs.NextRecordset Is Nothing)
// now is safe to read the out param
response.write oCmd("@MyOutput")
slolife
  • 19,520
  • 20
  • 78
  • 121
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I guess it makes sense, although it seems that many people want to use the output params BEFORE the resultset: http://www.dominicpettifer.co.uk/Blog/8/output-parameters-and-recordsets-from-a-stored-procedure But even with this knowledge, it is not working for me. I have tried calling .NextRecordset, .Close before getting the param or just getting it after I loop through the results. Nothing works. Any other ideas? – slolife Nov 09 '11 at 00:36
  • Are you sure you interated over *all* recordset? Did the NextRecordset call returned false? Until it returns false, you need to keep iterating. – Remus Rusanu Nov 09 '11 at 01:06
  • Yes I add some test code right after .Execute() to loop thru each records using .MoveNext(). I tried getting parameter there as well as then closing the recordset and then getting param or calling .NextRecordset() and getting param. All returned NULL for the param. – slolife Nov 10 '11 at 00:43
  • See my edit, you must *iterate all rows in all result sets*, is not clear from your explanation whether you do that or not. – Remus Rusanu Nov 10 '11 at 01:31
  • I will give that a try, and thank you for the code example because that is a great way to do it. But I do only have 1 resultset and I did iterate over ALL of them and then did NextRecordset (which should be the end) and it did not work. – slolife Nov 10 '11 at 17:16
  • I dug a little more and you have the right idea, but please update your example to match ASP classic/VBSCript/ADO. NextRecordset does not return a bool, but instead returns a recordset object. So the logic need to say while (Not rs.NextRecordset is Nothing). Once I did that, it did the trick. – slolife Nov 10 '11 at 17:58
  • Feel free edit the post and put the correct classic ADO code. It's been +10 years since I haven't wrote a classic ADO command, I'm well out of touch. – Remus Rusanu Nov 10 '11 at 18:02
  • Really glad I found this article! It helped me return a param and recordset after all these years of thinking it was impossible! I am so happy - go Stack Overflow :-) – David Coster Dec 03 '14 at 06:22
  • The OP is using VBScript with Classic ASP not JScript so this is just confusing. – user692942 Feb 24 '15 at 09:35
  • This is complete nonsense the only reason an `ParamOutput` parameter is not available is if it is located after a `SELECT` statement in the stored procedure. Be careful not to use something like `SELECT @p33 = 1` as this will create a `ADODB.Recordset` and block access to the `ParamOuput` parameter until the recordset is closed. If you want to access an ouput parameter before the execution of a query that returns a recordset use `SET @p33 = 1` instead. – user692942 Feb 24 '15 at 09:56
  • @Lankymart if you think you have a better answer, add it as an answer. – Remus Rusanu Feb 24 '15 at 09:58
  • The know something as this is being linked to other questions and people are using it as reference I think I will. – user692942 Feb 24 '15 at 09:59
  • You should make sure you know the correct answer of a procedure that does `set @p = 1; select ... from ...; set @p=2;`. – Remus Rusanu Feb 24 '15 at 10:00
  • @RemusRusanu Agreed but then it's not rocket science, you call it before `Set oRS = oCmd.Execute()` it's going to be `1` if you call it after `oRS.State = adStateClosed` it's going to be `2`. – user692942 Feb 24 '15 at 12:43
  • @Lankymart "Is going to be 1" you now what, why don't you test that and tell us what it is? – Remus Rusanu Feb 24 '15 at 14:35
  • @RemusRusanu I get your point, I'm talking about the order of the executing T-SQL code. When you call `.Execute()` (from VBScript) you will only ever be able to get an output of `2` because the call is not made until `.Execute()` is fired that is a given, but why would you want to do that anyway? – user692942 Feb 24 '15 at 14:49
  • 2
    It is essentiel to never access the return and output parameters before you call `NextRecordset()` on the last recordset (not even for debugging purposes!). Otherwise, they will remain empty (`vbEmpty`!) after the last call to `NextRecordset()` and not contain the true values! – Markus Nißl Jul 26 '19 at 08:25
3

As far as I can test the only thing that matters this is the CursorLocation. As long as the CursorLocation is set to adUseClient (3), the output parameters can be accessed anytime after the Execute. Any of the following do that

oConn.CursorLocation = adUseClient

or

oRs.CursorLocation = adUseClient

This is tested on SQL 2008 R2, IIS/ASP on Windows 7 and with SQLOLEDB provider.

amit_g
  • 30,880
  • 8
  • 61
  • 118
  • Is there a way to specify adLockReadOnly and adOpenForwardOnly for the recordset that is created? I am looking at http://www.w3schools.com/ado/met_comm_execute.asp and it doesn't say there is. – slolife Nov 08 '11 at 22:44
  • These are the defaults, so it should be fine. – amit_g Nov 08 '11 at 22:51
  • Changing it to oCmd.Execute() did not work (output param is still null after the execute) – slolife Nov 08 '11 at 23:02
  • 3
    It appears that the output parameters are not available until I've called oRS.NextRecordset to get thru all of the recordsets. But if I loop thru each record in the first recordset, I still cannot get the output parameter after .NextRecordset – slolife Nov 08 '11 at 23:25
  • ty, its ok for me – Esperento57 Feb 15 '23 at 09:40