Database Code
  Home arrow Database Code arrow Execute stored proc having input and outpu...
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? 
DATABASE CODE

Execute stored proc having input and output params, returned recordset and return value
By: aspfree
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 18
    2000-02-13

    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


    Return Values how-to Execute a Stored Proc's

    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 Database Code Articles
    More By aspfree

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! Trial download: IBM Rational Functional Tester V7.0.1

    Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
    FREE! Go There Now!


    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! 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!


    NEW! Don't wait! Try the Rational Application Developer (RAD) v7.5 open beta code today

    Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
    FREE! Go There Now!


    NEW! Using Rational Business Developer to enhance your developer productivity

    Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components.
    FREE! Go There Now!


    NEW! Webcast: Quickly provide customized, integrated user interfaces with Lotus Notes 8

    IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community.
    FREE! Go There Now!


    Role of Integrated Requirements Management in Software Delivery

    As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
    FREE! Go There Now!


    NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment

    Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
    FREE! Go There Now!


    NEW! Develop Systems Software Assets with IBM Rational Asset Manager

    Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
    FREE! Go There Now!


    NEW! "ebook: Exploring IBM SOA Technology & Practice

    Learn field-tested SOA principles, methodology, technology and implementation from the global SOA market leader - in a new e-book by an IBM SOA expert. Written by IBM Certified SOA Solution Designer Bobby Woolf, "Exploring IBM SOA Technology & Practice" is the ultimate insider's guide to SOA - a PDF e-book packed cover to cover with IBM's specific advice on how to make your SOA implementation a success.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    DATABASE CODE ARTICLES

    - Deployment of the MobiLink Synchronization M...
    - MobiLink Synchronization Wizard in SQL Anywh...
    - Finding Matching Records in Data Access Pages
    - Using the AccessDataSource Control in VS 2005
    - A Closer Look at ADO.NET: The Command Object
    - A Closer Look at ADO.NET: The Connection Obj...
    - Using ADO to Communicate with the Database, ...
    - Code Snippets: Counting Records
    - Constraints In Microsoft SQL Server 2000
    - Multilingual entries into a DB and to be dis...
    - Two combos, one textbox example
    - ADO Recordset Paging
    - SQL Server Database Creator - .NET Version
    - Getting A List of Tables From SQL Server
    - Discussion & Listserv Module by Mike Eck...





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