Web Development

  Homes arrow Web Development arrow Professional Active Server Pages 3.0 Part 3 -...
 Webmaster Tools
 
Base64 Encoding 
Browser Settings 
CSS Coder 
CSS Navigation Menu 
Datetime Converter 
DHTML Tooltip 
Dig Utility 
DNS Utility 
Dropdown Menu 
Fetch Content 
Fetch Header 
Floating Layer 
htaccess Generator 
HTML to PHP 
HTML Encoder 
HTML Entities 
IP Convert 
Meta Tags 
Password Encryption
 
Password Strength
 
Pattern Extractor 
Ping Utility 
Pop-Up Window 
Regex Extractor 
Regex Match 
Scrollbar Color 
Source Viewer 
Syntax Highlighting 
URL Encoding 
Web Safe Colors 
Forums Sitemap 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
WEB DEVELOPMENT

Professional Active Server Pages 3.0 Part 3 - Stored Procedures
By: Developer Shed
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 2
    2004-02-03

    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
     
     

    SEARCH DEV MECHANIC

    TOOLS YOU CAN USE

    advertisement

    Professional Active Server Pages 3.0 Part 3 - Stored Procedures
    by Wrox Books

    So why should we create and use a stored procedure instead of just creating a SQL string on the fly, as in the example shown above? Well, there are several reasons:
    • A stored procedure is compiled by the database. This produces an execution plan, so the database knows exactly what it's going to do. This makes the execution of the procedure faster.
    • Stored procedures are often cached by the database, thus making them faster to run, as they don't have to be read from disk. Not all databases support this caching mechanism - Microsoft Access doesn't, but SQL Server does.
    • You can make your data a little bit more secure by specifying that your database tables can be modified only by stored procedures. This means that potentially dangerous SQL operations generated on the fly may not be performed.
    • You avoid cluttering your ASP code with lengthy SQL statements. This makes the ASP code easier to maintain.
    • You can keep all of the SQL code together, on the server.
    • You can use output parameters in a stored procedure, which allows you to return both a recordset and other values.

    As a general rule, stored procedures will nearly always be quicker than their equivalent SQL statements.

    To use a stored procedure you just put the name of the stored procedure as the command text, and set the type accordingly. For example, consider the previous example of updating book prices. If we created a stored procedure on SQL Server, it might look like this:

    CREATE PROCEDURE usp_UpdatePrices
    AS
    UPDATE Titles
    SET    Price = Price * 1.10
    WHERE  Type='Business'
    

    For a Microsoft Access database you can create a simple update query to do the same task:

    To run this stored procedure from an ASP page, you'd simply use the following code:

    Set cmdUpdate = Server.CreateObject("ADODB.Command")
    cmdUpdate.ActiveConnection = strConn
    cmdUpdate.CommandText = "usp_UpdatePrices"
    cmdUpdate.CommandType = adCmdStoredProc
    cmdUpdate.Execute , , adExecuteNoRecords
    

    This simply runs the stored procedure. No recordset is returned, because we are only updating data - remember, there's no point creating a recordset unless one is needed.

    As it stands though, this procedure isn't very flexible since it only deals with one book type. What would be better would be a procedure that allows us to select the book type so we don't have to create a procedure for each book type. And while we're at it, we might as well remove the fixed 10% update, and allow this to be flexible too. So, how do we achieve this - simple, with parameters.

    Parameters

    Parameters to stored procedures are just like parameters (or arguments, depending on your preferred term) to procedures and functions. These allow values to be passed into a function, and then the function can use the value. Stored procedures in SQL Server (and other databases, including Access) both have this facility.

    To allow the procedure to cope with multiple book types, and even allow the user to specify the price increase (or decrease), we need to add some parameters:

    CREATE PROCEDURE usp_UpdatePrices
    @Type      Char(12),
    @Percent   Money
    AS
    UPDATE Titles
    SET    Price = Price * (1 + @Percent / 100)
    WHERE  Type = @Type
    

    The stored procedure usp_UpdatePrices now takes two parameters:

    • One for the book type (@Type)
    • One for the percentage change in price (@Percent)

    Just like a VBScript function, these parameters are variables. However, unlike VBScript and other scripting languages where all the variables are variants, SQL variables all have specific types (Char, Money, etc). They must also follow the naming convention for SQL variables, which means they must start with an @ symbol.

    Notice that we've allowed the percentage to be supplied as a whole number (for example 10 for 10%), instead of it's fractional value. This just makes the procedure more intuitive to use.

    The Parameters Collection

    So now we have a stored procedure with parameters, how do we actually call this from ADO? You've already seen how to call stored procedures without parameters using the Command object, and that doesn't change. What changes is the use of the Parameters collection.

    The Parameters collection contains a Parameter object for each parameter in the stored procedure. However, ADO doesn't automatically know what these parameters are, so you have to create them, using the CreateParameter method, which takes the following form:

    Set Parameter = Command.CreateParameter (Name, [Type], [Direction], 
    [Size], [Value])
    

    The arguments are:

    Once the parameter is created it can be appended to the Parameters collection. For example:

    Set parValue = cmdUpdate.CreateParameter("@Type", adVarWChar, _
    adParamInput, 12, "Business")
    cmdUpdate.Parameters.Append parValue
    Set parValue = cmdUpdate.CreateParameter("@Percent", adCurrency, _
    adParamInput, , 10)
    cmdUpdate.Parameters.Append parValue
    

    There's no need to explicitly create an object to hold the parameter - the default type of Variant works well enough here. If you don't want to create a variable, you can also take a shortcut:

    cmdUpdate.Parameters.Append = _
    cmdUpdate.CreateParameter("@Percent", adCurrency, _
    adParamInput, , 10)
    

    This uses the fact that the CreateParameter method returns a Parameter object, and the Append method accepts a Parameter object. This method is marginally faster than using a variable, but does make your lines of code longer and therefore harder to read. You can use whichever method you prefer.

    You don't have to set the value of the parameter at the time you create the parameter, since once the parameter is appended to the Parameters collection it remains in the collection. You can therefore set the value any time before the command is run. For example:

    cmdUpdate.Parameters.Append = _
    cmdUpdate.CreateParameter ("@Percent", adCurrency, adParamInput)
    cmdUpdate.Parameters("@Percent") = 10
    

    In the previous chapter we mentioned that there are several ways of accessing values in collections, and the Parameters collection is no different. The above example uses the name of the parameter to index into the collection, but you could equally use the index number:

    cmdUpdate.Parameters(0) = 10
    

    This sets the value of the first (collections are zero-based) parameter in the collection. The index number method is marginally faster that the name method, but obviously the name method makes your code much clearer to read.

    One point that is important to note is that the parameters in the Parameters collection must match the order of the parameters in the stored procedure.

    Running Parameter Commands

    Once the parameters have been added, the command can now be run and these parameter values will be passed into the stored procedure. So now, you can make a nice page that updates selected book types. For example, let's imagine a page called UpdatePrices.asp, that looks like this when run:

    You could easily build this page dynamically, getting a list of book types from the database. The first thing we do is include the file Connection.asp - this contains the connection string (held in strConn) as well as the reference to the ADO constants, which we discussed in the previous chapter:

    <!-- #INCLUDE FILE="../Include/Connection.asp" -->
    

    Next, we can build the form (we won't show the large text body here, but it's in the sample file). The form calls the ASP file called StoredProcedure.asp:

    <FORM NAME="UpdatePrices" Method="Post" ACTION="StoredProcedure.asp">
    <TABLE>
    <TR>
    <TD>Book Type:</TD>
    <TD>
    <SELECT NAME="lstTypes"></TD>
    

    Now we can start the ASP script, to read in the book types from the titles table. We use a SQL string to return only the unique book types, and then put the returned values into HTML OPTION tags:

    <%
    Dim rsTypes
    Dim strSQL
    Dim strQuote
    ' Predefine the quote character
    strQuote = Chr(34)
    ' Create a recordset of the types
    Set rsTypes = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT DISTINCT type FROM titles"
    rsTypes.Open strSQL, strConn
    ' Create the book types
    While Not rsTypes.EOF
    Response.Write "<OPTION VALUE=" & strQuote & _
    rsTypes("Type") & strQuote & ">" & rsTypes("Type")
    rsTypes.MoveNext
    Wend
    rsTypes.Close
    Set rsTypes = Nothing
    %>
    

    Once the book types have been displayed, we can construct the remainder of our form, including a text box allowing the user to enter the percentage change:

    </SELECT>
    </TD>
    </TR>
    <TR>
    <TD>Percent Value</TD>
    <TD><INPUT NAME="txtPercent" TYPE="TEXT"></TD>
    </TR>
    </TABLE>
    <P>
    <INPUT TYPE="Submit" VALUE="Run Query">
    </FORM>
    

    Let's now look at StoredProcedure.asp, the ASP page that the Run Query button calls. The first thing we do is declare the variables, and extract the book type and percentage from the calling form:

    <%
    Dim cmdUpdate
    Dim lngRecs
    Dim strType
    Dim curPercent
    ' Get the form values
    strType = Request.Form("lstTypes")  
    curPercent = Request.Form("txtPercent")
    

    Now we can display some confirmation to the user of what's happening:

    ' Tell the user what's being done
    Response.Write "Updating all books" & _
    " of type <B>" & strType & "</B>" & _
    " by " & curPercent & "%<P>"
    

    Now come the guts of the code, where we create the Command object and the parameters:

    Set cmdUpdate = Server.CreateObject("ADODB.Command")
    ' Set the properties of the command
    With cmdUpdate
    .ActiveConnection = strConn
    .CommandText = "usp_UpdatePrices"
    .CommandType = adCmdStoredProc
    

    We use the shortcut method of creating and adding the parameters, using the values we've extracted from the previous page's form:

    ' Add the parameters
    .Parameters.Append .CreateParameter("@Type", adVarWChar, _
    adParamInput, 12, strType)
    .Parameters.Append .CreateParameter("@Percent", adCurrency, _
    adParamInput, , curPercent)
    

    And now we can run the stored procedure:

    ' Execute the command
    .Execute lngRecs, , adExecuteNoRecords
    End With
    

    And just for confirmation, we can tell the user how many records were updated:

    ' And finally tell the user what's happened
    Response.Write "Procedure complete. " & lngRecs _
    & " records were updated."
    Set cmdUpdate = Nothing
    %>
    

    So there we have two simple pages. The first builds a list of items to select and the second uses one of those items as a value in the update. This is the basis for many ASP pages that need to display and update data like this.

    Passing Parameters as an Array

    The Parameters collection is all very well, but it's a bit cumbersome (especially for those two fingered typists). Luckily there's a quick shortcut method, using the Parameters argument of the Execute method. For example, let's call our tame stored procedure, usp_UpdatePrices, but without using the Parameters collection.

    We create the Command object and set it's properties in the same way as before:

    Set cmdUpdate = Server.CreateObject("ADODB.Command")
    ' Set the properties of the command
    With cmdUpdate
    .ActiveConnection = strConn
    .CommandText = "usp_UpdatePrices"
    .CommandType = adCmdStoredProc
    

    But here's where the difference lies. Instead of creating parameters and adding them to the collection, we simply pass the parameters into the stored procedure via the Execute method:

    ' Execute the command
    .Execute lngRecs, Array(strType, curPercent), adExecuteNoRecords
    End With
    

    This utilizes the Array function, which turns individual variables into an array, suitable for passing into this method call. As with every shortcut, there are of course some disadvantages to this method:

    • You can only use input parameters. Since you cannot specify type and direction of parameters they default to input parameters.
    • This method is slower if you intend to call the stored procedure several times, since ADO will ask the data store what the parameters are, and what data types they use.

    The difference in speed between the collection method and the array method is so small that it's hardly noticeable, so if you only have input parameters you can use whichever method you prefer. I actually prefer the long-winded method of the Parameters collection, because it makes it more explicit what the properties of the parameters are.

    Output Parameters

    We've seen that you can get the number of records affected by a command, but what if you want more information, but still don't want to return a recordset. Perhaps you want two or three values returned from a stored procedure, but don't want to go to the overhead of creating a recordset. If this is the case you can define a parameter as an output parameter, where the value is supplied by the stored procedure.

    For example, let's consider our price update routine. Suppose we want to find out the maximum book price after the update has taken place. Our stored procedure could be changed like this:

    CREATE PROCEDURE usp_UpdatePricesMax
    @Type      Char(12),
    @Percent   Money,
    @Max       Money      OUTPUT
    AS
    BEGIN
    UPDATE Titles
    SET    Price = Price * (1 + @Percent / 100)
    WHERE  Type=@Type
    SELECT @Max = MAX(Price)
    FROM   Titles
    END
    

    This just runs a simple SELECT after the update and places the value in the output parameter.

    We can now change the code in StoredProcedure.asp accordingly to retrieve the value of @Max:

    <%
    Dim cmdUpdate
    Dim lngRecs
    Dim strType
    Dim curPercent
    Dim curMax
    ' Get the form values
    strType = Request.Form("lstTypes")
    curPercent = Request.Form("txtPercent")
    ' Tell the user what's being done
    Response.Write "Updating all books" & _
    " of type <B>" & strType & "</B>" & _
    " by " & curPercent & "%<P>"
    Set cmdUpdate = Server.CreateObject("ADODB.Command")
    ' Set the properties of the command
    With cmdUpdate
    .ActiveConnection = strConn
    .CommandText = "usp_UpdatePricesMax"
    .CommandType = adCmdStoredProc
    

    We simply add another parameter to the collection, but this time specifying it as an output parameter. Note that we don't give it a value. That's because the value will be supplied by the stored procedure - remember, it's an output parameter:

    ' Add the parameters
    .Parameters.Append .CreateParameter ("@Type", adVarWChar, _
    adParamInput, 12, strType)
    .Parameters.Append .CreateParameter ("@Percent", adCurrency, _
    adParamInput, , curPercent)
    .Parameters.Append .CreateParameter ("@Max", adCurrency, _
    adParamOutput)
    ' Execute the command
    .Execute lngRecs, , adExecuteNoRecords
    

    Once the procedure is executed we can retrieve the value from the collection:

    ' Extract the output parameter, which the stored
    ' procedure has supplied to the parameters collection
    curMax = .Parameters("@Max")
    End With
    ' And finally tell the user what's happened
    Response.Write "Procedure complete. " & lngRecs & _
    " records were updated.<P>"
    Response.Write "The highest price book is now " & _
    FormatCurrency(curMax)
    Set cmdUpdate = Nothing
    %>
    

    If there is more than one output parameter, then they can be accessed in the same way. You can use the Parameter name or the index number to extract the value from the collection.

    Return Values

    Return values from functions are handled differently from the way return values from stored procedures are handled, and this often causes confusion. In user functions, we often return a Boolean value to indicate the success or failure of a function:

    If SomeFunctionName() = True Then
    ' Function succeeded
    

    When calling a stored procedure though, we can't use the same method, because the stored procedures are run using the Execute method, and this returns a recordset:

    Set rsAuthors = cmdAuthors.Execute
    

    If we can't get a return value, how do we determine if the stored procedure executed correctly? Well, if an error occurred this would be reported, and we could handle it with the error handling code shown in the previous chapter. But what about some sort of non-fatal logic error?

    For example, consider adding a new employee to the employee table. You don't want to stop two people with the same name being added, but you might want this situation flagged. Here's where we could use a return value, to indicate whether an employee with the same name already exists. The stored procedure might look like this:

    CREATE PROCEDURE usp_AddEmployee
    @Emp_ID      Char(9),
    @FName       Varchar(20),
    @MInit       Char(1),
    @LName       Varchar(30),
    @Job_Id      SmallInt,
    @Job_Lvl     TinyInt,
    @Pub_ID      Char(4),
    @Hire_Date   Datetime
    AS
    BEGIN
    DECLARE @Exists    Int        -- Return value
    -- See if an employee with the same name exists
    IF EXISTS(SELECT *
    FROM   Employee
    WHERE  FName = @FName
    AND    MInit = @MInit
    AND    LName = @LName)
    SELECT @Exists = 1
    ELSE
    SELECT @Exists = 0
    INSERT INTO Employee (emp_id, fname, minit, lname,
    job_id, job_lvl, pub_id, hire_date)
    VALUES (@Emp_Id, @FName, @MInit, @LName, @Job_ID, 
    @Job_Lvl, @Pub_ID, @Hire_Date)
    RETURN @Exists
    END
    

    The first thing this procedure does is check to see if an employee with the same name exists, and sets a variable accordingly - it will be 1 if the employee exists, and 0 otherwise. The employee is then added to the table, and the Exists value is returned as the return value.

    Notice that although we are returning a value we don't declare it as a parameter to the stored procedure.

    The ASP code to call this procedure would look like:

    <!-- #INCLUDE FILE="../Include/Connection.asp" -->
    <%
    Dim cmdEmployee
    Dim lngRecs
    Dim lngAdded
    Set cmdEmployee = Server.CreateObject("ADODB.Command")
    ' Set the properties of the command
    With cmdEmployee
    .ActiveConnection = strConn
    .CommandText = "usp_AddEmployee"
    .CommandType = adCmdStoredProc
    ' Create the parameters
    ' Notice that the return value is the first parameter
    .Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, _
    adParamReturnValue) 
    .Parameters.Append .CreateParameter ("@Emp_Id", adChar, _
    adParamInput, 9)
    .Parameters.Append .CreateParameter ("@FName", adVarWChar, _
    adParamInput, 20)
    .Parameters.Append .CreateParameter ("@MInit", adChar, _
    adParamInput, 1)
    .Parameters.Append .CreateParameter ("@LName", adVarWChar, _
    adParamInput, 30)
    .Parameters.Append .CreateParameter ("@Job_Id", adSmallInt, _
    adParamInput)
    .Parameters.Append .CreateParameter ("@Job_Lvl", _
    adUnsignedTinyInt, adParamInput)
    .Parameters.Append .CreateParameter ("@Pub_ID", adChar, _
    adParamInput, 4)
    .Parameters.Append .CreateParameter ("@Hire_Date", adDBTimeStamp, _
    adParamInput, 8)
    ' Set the parameter values
    .Parameters("@Emp_Id") = Request.Form("txtEmpID")
    .Parameters("@FName") = Request.Form("txtFirstName")
    .Parameters("@MInit") = Request.Form("txtInitial")
    .Parameters("@LName") = Request.Form("txtLastName")
    .Parameters("@Job_ID") = Request.Form("lstJobs")
    .Parameters("@Job_Lvl") = Request.Form("txtJobLevel")
    .Parameters("@Pub_ID") = Request.Form("lstPublisher")
    .Parameters("@Hire_Date") = Request.Form("txtHireDate")
    ' Run the stored procedure
    .Execute lngRecs, , adExecuteNoRecords
    ' Extract the return value
    lngAdded = .Parameters("RETURN_VALUE")
    End With
    Response.Write "New employee added.<P>"
    If lngAdded = 1 Then
    Response.Write "An employee with the same name already exists."
    End If
    Set cmdEmployee = Nothing
    %>
    

    The important thing to note is that the return value is created as the first parameter in the collection. Even though the return value doesn't appear as a parameter in the stored procedure, it is always the first Parameter in the Parameters collection.

    So, just to stress this confusing point:

    Return values from stored procedures must be declared as the first parameter in the Parameters collection, with a direction of adParamReturnValue.

    Using Return Values

    Now this is defined we could have an initial form like this:

    Pressing the Add Employee button would then generate:

    Adding the same details again (with a different employee ID) gives this:


    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 Web Development Articles
    More By Developer Shed

       

    WEB DEVELOPMENT ARTICLES

    - On Page SEO for New Domains
    - Improve Your Site`s Speed
    - Safari Books Online Review
    - Creating an Estore From the Ground Up
    - Most Common SEO Mistakes Developers Make
    - Making the Most of Your Titles and Meta Desc...
    - Five Ways Using Flash Can Damage Your Site
    - A Web Designer`s Guide to Colors
    - Use Webstarts to Create a Free Site
    - More Than Just Looks. How Your Web Design C...
    - How to Design Content Pages
    - Mint Review
    - Make Your WordPress Website Look Professional
    - How to Create a Mobile Web Site
    - Meta Tags: Still Useful?

    Developer Shed Affiliates

     



    © 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap