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!


    IBM DB2 Deep Compression ROI Tool

    The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times.
    FREE! Go There Now!


    NEW! Driving Business Success with Rational Process Library

    Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance.
    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! IBM Rational ClearCase Innovator's Series

    Learn from the best! Find out how developers use Rational ClearCase to be more flexible, innovative and deliver higher quality code in the Rational ClearCase Power Users eKit. This complimentary eKit provides a collection of materials, like articles, whitepapers, and demos that can help you become a power user of Rational ClearCase.
    FREE! Go There Now!


    NEW! Integrating XML into Your Enterprise Using Data Federation

    XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
    FREE! Go There Now!


    NEW! Rational Build Forge Express eKit

    Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast.
    FREE! Go There Now!


    NEW! The dirty dozen: preventing common application-level hack attacks

    As organizations have grown increasingly dependent on online software, the risk of malicious attacks has also become far more serious. Fortunately, well-governed organizations can protect their Web applications by injecting vulnerability assessments and ethical hacks into their software development and delivery processes. This paper describes 12 of the most common hacker attacks and provides basic rules that you can follow to help create more hack-resistant Web applications.
    FREE! Go There Now!


    NEW! The role of integrated requirements management in software delivery

    This paper is about the critical role that a discipline called integrated require­ments management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrat­ing, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way.
    FREE! Go There Now!


    NEW! Webcast: Accelerating Software Innovation with System z

    Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
    FREE! Go There Now!


    NEW! Webcast: Application security testing and Web compliance

    Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
    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-2009 by Developer Shed. All rights reserved. DS Cluster 6 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek