ASP Code
  Home arrow ASP Code arrow Return Values how-to Execute a Stored Proc...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ASP CODE

Return Values how-to Execute a Stored Proc's
By: Adrian Forbes
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 3
    2003-01-01

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    This demo It's called ReturnValue.asp and shows you how to execute a stored procedure that has input params, output params, a returned recordset and a return value.

    <!-- Author: John Bailey -->

    <%@ Language=VBScript %>

    <%
    'CODE TO CREATE THE STORED PROCEDURE THAT THIS ASP ACCESSES
    'Just remove all comments after this line, paste into the SQL query analyzer and run.

    '-- insures you use the right database
    'use pubs
    'GO
    '
    '-- Creates the procedure
    'create procedure sp_PubsTest
    '
    '-- declare three parameter variables
    '  @au_lname varchar (20), 
    '  @intID int,
    '  @intIDOut int OUTPUT
    '
    'AS
    '
    'SELECT @intIDOut = @intID + 1
    '
    'SELECT *
    'FROM authors
    'WHERE au_lname LIKE @au_lname + '%'

    'RETURN @intID + 2

    %>



    <%

    'THIS IS THE ASP CODE. Just run from the server.

    Option Explicit

    Dim CmdSP
    Dim adoRS
    Dim adCmdSPStoredProc
    Dim adParamReturnValue
    Dim adParaminput
    Dim adParamOutput
    Dim adInteger
    Dim iVal
    Dim oVal
    Dim adoField
    Dim adVarChar

    adCmdSPStoredProc = 4
    adParamReturnValue = 4
    adParaminput = 1
    adParamOutput = 2
    adInteger = 3
    adVarChar = 200

    iVal = 5
    oVal = 3


      '-- Create a command object --
      set CmdSP = Server.CreateObject("ADODB.Command")

      '-- Make an ODBC connection to the (local) SQL server,
      '-- connecting to the Pubs database with the default sa login and empty password
      CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
     

      '-- define the name of the command 
      CmdSP.CommandText = "sp_PubsTest"
     
     
      '-- define the type of the command as a stored procedure (numeric value = 4)
      CmdSP.CommandType = adCmdSPStoredProc
     
     
      '-- define the first parameter - the one the procedure will return
      '-- the calls are:
      '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
      '--   CmdSP.CreateParameter(): creates the parameter using the values given:
      '--      "RETURN_VALUE" is the name of the parameter for later reference
      '--      adInteger (value = 3) indicates this parameter is an integer datatype
      '--      adParamReturnValue (value = 4) indicates this parameter is expected to be returned
      '--      4 is an arbitrary initial value for this parameter

      CmdSP.Parameters.Append CmdSP.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)


      '-- define the first parameter - the one the procedure will return
      '-- the calls are:
      '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
      '--   CmdSP.CreateParameter(): creates the parameter using the values given:
      '--      "@au_lname" is the name of the parameter for later reference
      '--      adVarChar (value = 200) indicates this parameter is a string datatype
      '--      adParamInput (value = 1) indicates this parameter is for input
      '--      20 is the size of the string in characters
      '--      "M" is an arbitrary initial value for this parameter
     
      CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname", adVarChar, adParaminput, 20, "M")


      '-- define the first parameter - the one the procedure will return
      '-- the calls are:
      '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
      '--   CmdSP.CreateParameter(): creates the parameter using the values given:
      '--      "@intID" is the name of the parameter for later reference
      '--      adInteger (value = 3) indicates this parameter is an integer datatype
      '--      adParamInput (value = 1) indicates this parameter is for input
      '--      the blank is a failure to declare the size of the variable
      '--      iVal is an arbitrary initial value for this parameter, placed with the variable
     
      CmdSP.Parameters.Append CmdSP.CreateParameter("@intID", adInteger, adParamInput, , iVal)


      '-- define the first parameter - the one the procedure will return
      '-- the calls are:
      '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
      '--   CmdSP.CreateParameter(): creates the parameter using the values given:
      '--      "@intIDOut" is the name of the parameter for later reference
      '--      adInteger (value = 3) indicates this parameter is an integer datatype
      '--      adParamOutput (value = 2) indicates this parameter is expected to return an output
      '--      oVal is an arbitrary initial value for this parameter, placed with the variable oVal
     
      CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut", adInteger, adParamOutput, oVal)
     
     
      '-- execute the command
      Set adoRS = CmdSP.Execute


    '-- loop through the returned recordset
    While Not adoRS.EOF

      '-- loop through the field collection, reporting name and contents
      for each adoField in adoRS.Fields
        Response.Write adoField.Name & "=" & adoField.Value & "<br>" & vbCRLF
      Next
      Response.Write "<br>"
      adoRS.MoveNext
    Wend


    '-- move to the parameter recordset
    Set adoRS = adoRS.NextRecordset


    '-- report parameter values, accessing each by name
    Response.Write "<p>@intIDOut =
    " & CmdSP.Parameters("@intIDOut").Value & "</p>"
    Response.Write "<p>Return value = " & CmdSP.Parameters("RETURN_VALUE").Value & "</p>"


    '-- tidy up the handles
    Set adoRS = nothing
    Set CmdSP.ActiveConnection = nothing
    Set CmdSP = nothing
    %>


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

    More ASP Code Articles
    More By Adrian Forbes

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! Accelerating Software Innovation on i on Power Systems

    Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
    FREE! Go There Now!


    NEW! Achieving True Agility -- How process can change the behavior of your tools

    Achieving true agility is a never-ending effort. We will showcase how you can become agile incrementally, a few practices at the time.Which practices should any agile team strive to adopt? What additional practices should you consider based on your needs to scale? Adopting practices are however made much easier with the right tool support. What about if your tools adapt to your practices? We will take a look at how the Jazz technology can be leveraged to make your process change the behavior of your tools.
    FREE! Go There Now!


    NEW! Download IBM WebSphere Portal V6.1 beta code

    Download the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization.
    FREE! Go There Now!


    NEW! Evaluate IBM Rational Developer for System i V7.1

    Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
    FREE! Go There Now!


    NEW! Evaluate IBM Rational Software Analyzer V7.0

    Download a free trial version of IBM Rational Software Analyzer Developer Edition V7.0 to identify bug defects earlier in the software development cycle. Rational Software Analyzer is an extensible software development solution that reduces the expense of bug-fixes by enabling static analysis code reviews and bug identification very early in the development cycle.
    FREE! Go There Now!


    NEW! Hello World: WebSphere Service Registry and Repository

    Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
    FREE! Go There Now!


    NEW! Info 2.0: Harnessing the power of Web 2.0 and Enterprise Mashups

    Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started.
    FREE! Go There Now!


    NEW! Rational Talks to You: Manage RUP-based CMMI initiatives

    Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
    FREE! Go There Now!


    NEW! Software Change and Configuration Management Solution Guidelines

    This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
    FREE! Go There Now!


    NEW! Trial download: IBM Lotus Forms V3.0

    Get a free trial download of IBM Lotus Forms V3.0 (formerly Workplace Forms), which provides a zero-footprint eForms solution to help you automate and move forms-based business processes off the desktop and onto the Web. With Lotus Forms, you can extend applications beyond the firewall by creating a single electronic form document ready for use in both thick and Web 2.0 thin client format.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    ASP CODE ARTICLES

    - ASP Forms
    - ASP: The Beginning
    - Getting Remote Files With ASP Continued
    - Inbox and Outbox Manipulation in ASP
    - Relational DropDownList Using VB.NET
    - Ad Tracking URL Hits
    - Use ViewState to display one record per page...
    - Send Email using ASP.NET formatted in HTML
    - ASP File Explorer
    - ASP/XML Interview questions by Srivatsan Sri...
    - Various methods of setting Date values to a ...
    - Conditional DataGrid Item and using checkbox...
    - Fill .NET Listbox with SQL DataReader
    - Filling Dropdown box using Code-Behinds in C#
    - FLAMES code sample written in .NET What is F...





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 5 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek