HOWTO: Get Records and Return Value from Stored Proc Using DE

• Microsoft Visual InterDev, version 6.0

SUMMARY

If you have a stored procedure that returns a recordset and has a RETURN coded in it that you want to also capture, you can use the Data Environment (DE) to call the stored procedure and do a Reponse.Write to display the return value. However, you would most likely use the value to determine a course of action and not display it. This sample is done using server-side Active Server Pages (ASP)/Active Data Objects (ADO) and the Data Environment.

MORE INFORMATION

NOTE: This article uses the SQL Server database of Pubs. It assumes that you have used the ODBC icon on the Control Panel to create a valid DSN to Pubs. It also assumes that a project has been created and is open in Visual InterDev.

Creating The Stored Procedure

1. In the Data View tab, expand the Pubs table, expand the Stored Procedures section.



2. Double-click the byroyalty stored procedure. When it opens in the editor, click Select All the Edit menu to highlight all the code, then select Copy.



3. Close this stored procedure out of the editor.



4. Right-click the Stored Procedure heading in the Data View tab, and select New Stored Procedure.



5. Visual InterDev will create a new page with the following text:

   
   Create Procedure StoredProcedure2
   /*
      (
         @parameter1 datatype = default value,
       @parameter2 datatype OUTPUT
    )
   */ 
   As
     /* set nocount on */ 
    return 

   Paste the recently copied text at the beginning this newly created file. Immediately following the pasted text add the command RETURN.



6. Delete the text that Visual InterDev automatically inserted when you created the new stored procedure.



7. Change the first line of the stored procedure from "byroyalty" to "byroywret."



8. Close the new stored procedure. When prompted to save changes, click Yes.

   CREATE PROCEDURE byroywret @percentage int
   AS
   select au_id from titleauthor
   where titleauthor.royaltyper = @percentage

   return 

Adding The Data Connection

1. Right-click the Global.asa file and select Add Data Command.



2. In the drop-down list in the Connection: property, find the connection to Pubs and select it.



3. Click Yes when asked if you would like to continue.



4. Set the Database Object property to Stored Procedure, and use the Object Name list to select the newly created stored procedure.



5. Click OK. There should be a new item in the Global.asa called Command1. (Since this command is a stored procedure in the Pubs database, it will show up only if you expand Pubs in the Data Environment under the Global.asa file.)

Calling The Stored Procedure

1. Right-click the project name in Project Explorer and from the Add menu, select Active Server Page. Click Open. A new ASP page will now open in the editor.



2. Right-click the page and select properties. From the property page which appears under ASP Settings, select Enable Scripting Object Model.



3. Insert a blank line between the <META> and </HEAD> tags at the top of the page.



4. With the cursor positioned on the newly inserted blank line between the <META> and </HEAD> tags, click the Script Outline tab. If this tab is not present, click the View menu, select Other Windows and then click Script Outline.



5. From the HTML menu, select Script Block and click Server to insert a <SCRIPT></SCRIPT> block.



6. If your default scripting language is VBScript inside the newly created sub or function, add the following code:

   
   sub thisPage_onenter()
   
   'instantiate the DERuntime object
   set DE = Server.createobject("DERuntime.DERuntime")
        DE.init Application("DE")
   
   'get recordset and return value from Stored Proc
   set RS = DE.Recordsets("Command1")
        set parms = RS.activeCommand.parameters
   
        ' hard-code a value of 50 into the "percentage" parameter
        parms(1) = 50
   RS.open
   
        'grab the return value
        intRetVal = parms (0)
   
   Response.Write("The return value is :")
   Response.Write(intRetVal)
   
   ' loop through the Recordset (RS) and put values into a table
   Response.Write("<TABLE>")
   do until RS.eof
      Response.Write("<TR><TD>" & RS("au_id"))
      RS.MoveNext
   loop
   Response.Write("</TABLE>")
   
   end sub 

   If your default scripting language is JavaScript (ECMAScript) inside the newly created sub or function, add the following code:

   
    function thisPage_onenter() {
   
   //instantiate the DERuntime object
   var DE = Server.createobject("DERuntime.DERuntime");
        DE.init (Application("DE"));
   
   //get recordset and return value from Stored Proc
   var RS = DE.Recordsets("Command1");
        var parms = RS.activeCommand.parameters;
        parms(1) = 50;
   RS.open();
        intRetVal = parms (0);
   
   Response.Write("The return value is :");
   Response.Write(intRetVal);
   
   //loop through the Recordset (RS) and put values into a table
   Response.Write("<TABLE>");
   while (!RS.EOF) {
      Response.Write("<TR><TD>" + RS("au_id"))
      RS.MoveNext()
   }
   Response.Write("</TABLE>");
   } 




7. Right-click in the page and select View in Browser. Answer Yes to the prompt to save the changes.

REFERENCES

For additional information about how to use the Data Command against a Stored Procedure that returns only a RETURN value but no recordset, please see the following article in the Microsoft Knowledge Base:

Q190762 PRB: Cannot Access a Stored Procedure's Return Value from DTC

Additional query words:

Keywords : kbADO kbVisID600 kbGrpASP
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto