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  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
ASP Web Hosting  
ASP.NET Web Hosting 
Dedicated Servers 
Actuate Whitepapers 
VeriSign Whitepapers 
Windows Web Hosting
 
IBM® developerWorks 
Sun Developer Network 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
SunQuest
 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
     
    IBM developerWorks
     
    ADVERTISEMENT

    Free Web 2.0 Code Generator! Generate data entry and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

    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!


    Build Forge Express demo: Enabling software delivery excellence for small and midsized businesses

    This demonstration gives you an overview of IBM® Rational® Build Forge Express Edition, a global offering that provides a framework to automate and execute software processes. Rational Build Forge provides a software assembly line that can support all of your tools, technologies, and platforms so you can achieve a repeatable, reliable, and traceable build and release process.
    FREE! Go There Now!


    Check out the new Jazz space on developerWorks

    <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
    FREE! Go There Now!


    NEW! Application Development Tools for the Mainframe Developer

    You probably have thousands of lines of COBOL code loaded with business intelligence and being used to run your business, along with an army of developers maintaining these applications. Learn how to prepare your applications and developers so you can keep that competitive edge and move to a service-oriented architecture with the IBM Rational Enterprise Modernization solutions. Replay is available for 9 months.
    FREE! Go There Now!


    NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation

    Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
    FREE! Go There Now!


    NEW! Harnessing the power of SQL and Java for high performance data access

    Join this webcast to see how IBM Data Studio Developer and pureQuery can take the pain out of Java data access. uApplications developed using both Java and SQL have become a common requirement. Database connectivity using Java Database Connectivity (JDBC) to create an application is a multi-step tedious process, and tooling that covers both SQL and Java has been unavailable, until now. IBM Data Studio introduces the pureQuery platform: a high-performance, Java data access platform focused on simplifying the tasks of developing, managing, and optimizing database applications and services.
    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! Rational Asset Manager eKit

    Learn how to do more with your reusable assets with the free Rational Asset Manager eKit. The eKit includes demos on how Rational Asset Manager tracks and audits your assets in order to utilize them for reuse. Plus you’ll find white papers and a Webcast that discuss the challenges of a Service Oriented Architecture and how Rational Asset Manager can provide quick and effective solutions.
    FREE! Go There Now!


    NEW! Rational Talks to You: Scott Ambler on being agile in a global development environment

    Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
    FREE! Go There Now!


    NEW! Try IBM Rational Asset Manager V7.0 online!

    You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
    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!



    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...
    - Getting A List of Tables From SQL Server
    - SQL Server Database Creator - .NET Version
    - ADO Recordset Paging
    - Two combos, one textbox example
    - Discussion & Listserv Module by Mike Eck...





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway