asp 中调用存贮过程的一些例子.(1)

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, aclearcase/" target="_blank" >ccessing 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
%>





Running Dynamic Stored Procedures


--------------------------------------------------------------------------------

This article assumes that you know what a stored procedure is, and have had experience creating them and executing them via an ASP page. If this is not the case, be sure to read the following articles first:


Writing a Stored Procedure

Writing a Stored Procedure Part II
Now, one question that is commonly asked is, "How to I create a stored procedure that can execute dynamic SQL statements?" For example, you may want a stored procedure that takes, as an input, a particular WHERE clause, such that your stored procedure could be defined as:

CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
AS
    SELECT *
    FROM TableName
    WHERE @WHEREClause




Or perhaps you@#d like to be able to query a particular table based upon a parameter, like:

CREATE PROCEDURE MyProc
  (@TableName varchar(255),
   @FirstName varchar(50),
   @LastName varchar(50))
AS
    SELECT *
    FROM @TableName
    WHERE FirstName = @FirstName
      AND LastName = @LastName




In either case, if you try putting either of the above code snippets into a stored procedure, you@#ll get an error. To execute a dynamic SQL statement in a stored procedure, you need to use the EXEC function. The EXEC function takes a SQL string as a parameter, and executes that SQL statement.

So, when using the EXEC function, begin by declaring a varchar(255) variable named @SQLStatement. Then, assign your dynamic SQL statement to this variable, and, finally, use EXEC to execute the SQL statement! For example, the first example above should be changed to:

CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)
    
    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM TableName WHERE "
                              + @WHEREClause
    
    -- Execute the SQL statement
    EXEC(@SQLStatement)




The second example could be changed to:

CREATE PROCEDURE MyProc
  (@TableName varchar(255),
   @FirstName varchar(50),
   @LastName varchar(50))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)
    
    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM " +
                   @TableName + "WHERE FirstName = @#"
                   + @FirstName + "@# AND LastName = @#"
                   + @LastName + "@#"
    
    -- Execute the SQL statement
    EXEC(@SQLStatement)




Note that you have to surround the value of @FirstName and @LastName with single quotes, much like you do when building a SQL statement in an ASP page. Also note that if @FirstName or @LastName contain single quotes, an error will occur. Therefore, you should Replace the single quotes with two single quotes in your ASP page before calling the stored procedure (see How to Deal with Apostrophes in your SQL String for more details).

For a good application of using the EXEC statement, be sure to read: Using the IN Notation in Stored Procedures; for a thorough discussion on the efficiency of dynamic stored procedures, read The Speed of Dynamic Queries.

Also some things to consider, as noted by alert 4Guys reader Leo C.:

I have also used the EXEC function to be able to have dynamic stored procedures and found it very useful.
However, you must make clear that this only works with Microsoft SQL Server and not with Sybase or Oracle! I have tested this extensively, and this conventient feature is only available within MS SQL Server.

Also, I set the ODBC driver to ignore ANSI quotes, etc., although a different combination of doubel and single quotes may make this unnecessary.

Happy Programming!





Writing a Stored Procedure
By Nathan Pond


--------------------------------------------------------------------------------

Once You@#ve Finished Reading...
Once you@#ve completed this article be sure to check out Nathan Pond@#s follow-up article: Writing a Stored Procedure, Part II!  


If you@#re anything like me, you don@#t easily pick up on development techniques just by hearing about them. When I first installed my MS SQL server on my computer, it opened up a whole new world of features that I had never used. Among these were Stored Procedures. This article is designed to tell you how to begin writing stored procedures. I am using Microsoft SQL Server 7.0, but these examples should work in any SQL version.

Writing Stored Procedures doesn@#t have to be hard. When I first dove into the technology, I went to every newsgroup, web board, and IRC channel that I knew looking for answers. Through all the complicated examples I found in web tutorials, it was a week before I finally got a working stored procedure. I@#ll stop rambling now and show you what I mean:

Normally, you would call a database with a query like:

Select column1, column2 From Table1  


To make this into a stored procedure, you simple execute this code:

CREATE PROCEDURE sp_myStoredProcedure
AS
Select column1, column2 From Table1
Go




That@#s it, now all you have to do to get the recordset returned to you is execute the stored procedure. You can simply call it by name like this:

sp_myStoredProcedure  


Note: You can name a stored procedure anything you want, provided that a stored procedure with that name doesn@#t already exist. Names do not nee to be prefixed with sp_ but that is something I choose to do just as a naming convention. It is also somewhat a standard in the business world to use it, but SQL server does not require it.

Now, I realize you aren@#t gaining much in this example. I tried to make it simple to make it easy to understand. In part II of this article, we@#ll look at how it can be useful, for now let@#s look at how you can call a Stored Procedure with parameters.

Let@#s say that we want to expand on our previous query and add a WHERE clause. So we would have:

Select column1, column2 From Table1
Where column1 = 0  


Well, I know we could hard code the Where column1 = 0 into the previous stored procedure. But wouldn@#t it be neat if the number that 0 represents could be passed in as an input parameter? That way it wouldn@#t have to be 0, it could be 1, 2, 3, 4, etc. and you wouldn@#t have to change the stored procedure. Let@#s start out by deleting the stored procedure we already created. Don@#t worry, we@#ll recreate it with the added feature of an input parameter. There isn@#t a way that I@#m aware of to simply over-write a stored procedure. You must drop the current one and re-create it with the changes. We will drop it like this:

DROP PROCEDURE sp_myStoredProcedure  


Now we can recreate it with the input parameter built in:

CREATE PROCEDURE sp_myStoredProcedure
   @myInput  int
AS
Select column1, column2 From Table1
Where column1 = @myInput
Go




Ok, why don@#t we pause here and I@#ll explain in more detail what is going on. First off, the parameter: you can have as many parameters as you want, or none at all. Parameters are set when the stored procedure is called, and the stored procedure receives it into a variable. @myInput is a variable. All variables in a stored procedure have a @ symbol preceding it. A name preceded with @@ are global variables. Other than that, you can name a variable anything you want. When you declare a variable, you must specify its datatype. In this case the datatype is of type Int (Integer). Now, before I forget, here@#s how to call the stored procedure with a parameter:

sp_myStoredProcedure 0  


If you want more than one parameter, you seperate them with commas in both the stored procedure and the procedure call. Like so:

CREATE PROCEDURE sp_myStoredProcedure
   @myInput  int,
   @myString varchar(100),
   @myFloat
AS
.....
Go




And you would call it like this:

sp_myStoredProcedure 0, @#This is my string@#, 3.45  


Note: The varchar datatype is used to hold strings. You must specify the length of the string when you declare it. In this case, the variable is assigned to allow for 100 characters to be help in it.

Now, I@#m sure some of you are wondering if there is a difference for SQL calls coming from ASP. There really isn@#t, let@#s take our first stored procedure example and I@#ll show how it is called from ASP. If it wasn@#t a stored procedure, you would call it something like this:

<%
  
    dim dataConn, sSql, rs
    set dataConn = Server.CreateObject("ADODB.Connection")
    dataConn.Open  "DSN=webData;uid=user;pwd=password" @#make connection
    sSql = "Select column1, column2 From Table1"
    Set rs = dataConn.Execute(sSql) @#execute sql call

%>  


Now let@#s see how we call the stored procedure.

<%
    dim dataConn, sSql, rs
    set dataConn = Server.CreateObject("ADODB.Connection")
    dataConn.Open  "DSN=webData;uid=user;pwd=password" @#make connection
    sSql = "sp_myStoredProcedure"
    Set rs = dataConn.Execute(sSql) @#execute sql call

%>  


As you can see, the only difference is the query that is to be executed, which is stored in the sSql command. Instead of being the actual query, it is simply the name of the stored procedure. Now let@#s take a quick look at how you would call it with parameters. In our second example, we created the stored procedure to accept one integer parameter. Here@#s the code:

<%
    dim dataConn, sSql, rs, myInt
    myInt = 1 @#set myInt to the number we want to pass to the stored procedure
    set dataConn = Server.CreateObject("ADODB.Connection")
    dataConn.Open  "DSN=webData;uid=user;pwd=password" @#make connection
    sSql = "sp_myStoredProcedure " & myInt
    Set rs = dataConn.Execute(sSql) @#execute sql call

%>  


Well, that@#s all for this article. Sometime in the near future I plan on writing a second part that really dives into more specifics about stored procedures. I hope this is enough to get you started though. Feel free to e-mail me with any questions or comments about the article!

Happy Programming!

原文转自：http://www.ltesting.net