| 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 developerWorks - FREE Tools! | 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!
| | | | Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities. FREE! Go There Now!
| | | | In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information. FREE! Go There Now!
| | | | CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP. FREE! Go There Now!
| | | | Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly. FREE! Go There Now!
| | | | 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!
| | | | This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer. FREE! Go There Now!
| | | | In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset. FREE! Go There Now!
| | | | 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!
| | | | 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!
| | | | All FREE IBM® developerWorks Tools! | |