1

I have a stored procedure which returns a temporary table as well as the output parameter. I am returning values from SP

    CREATE PROCEDURE [dbo].[procedure_name] 
    (
     @Id int
     @ReturnCode int output
    )
AS
BEGIN
Create table #tbl_temp (
                        i INT identity, 
                        TestId INT
                    ) 
Insert into table #tbl_temp -- Inserting Records
Select from table #tbl_temp -- This resultSet will be my output

SET @ReturnCode = 9 // some int value
select * from #tbl_temp // returning the temporary table

END

In Classic ASP, To get the tempTable, the code written is like this

rs.open sp.execute()

In the above case, I cannot get the value of the "@ReturnCode".

I need both the values. How should I achieve this?

KloppForKop
  • 104
  • 1
  • 10
  • What is `sp` in your code sample? As far as I remember, ADO command object can handle both opening a recordset and returning output parameters. – Zohar Peled Jul 06 '17 at 06:29
  • SP is the Adodb Command Object. I need to know how I can get the recordset as well as output parameter. – KloppForKop Jul 06 '17 at 06:49
  • Well, it's ancient history (at least for me, last asp3 website I worked on was over 9 years ago), but [google never forgets](https://www.google.co.il/search?q=ado+command+output+parameter+example&rlz=1C1LENP_iwIL718IL718&oq=ado+command+get+output+para&aqs=chrome.1.69i57j0l2.10911j0j4&sourceid=chrome&ie=UTF-8). In One [link](https://www.sqlservercentral.com/Forums/Topic158116-20-1.aspx) I've found someone states that the recordset should be closed before you can access the output parameterse. Seems reasonable to me since I know the same thing happens in ado.net when executing a data reader. – Zohar Peled Jul 06 '17 at 07:01
  • You need to use a `ADODB.Command` object to define the parameters and execute the stored procedure. See https://stackoverflow.com/a/42070162/692942 *(example uses `RETURN` but can be substituted to use an `OUTPUT` parameter. The key to making it work is `RETURN` and `OUTPUT` parameter values can only be accessed after all recordsets have been returned and closed)*. – user692942 Jul 06 '17 at 07:13
  • 1
    This [example](https://stackoverflow.com/a/21698468/692942) shows how to define parameters rather then using `Refresh()` *(which I wouldn't recommend for production)*. In your case the parameter for the output would be defined as `Call .Parameters.Append(.CreateParameter("@ReturnCode", adInteger, adParamOutput, 4))`. Then after the recordsets has been returned and closed you would retrieve the value using `yourvariable = .Parameters("@ReturnCode").Value`. – user692942 Jul 06 '17 at 07:26
  • 1
    Thank you @Lankymart! it worked for me from the link you had provided. – KloppForKop Jul 06 '17 at 08:22

0 Answers0