When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Monday, October 23, 2000

Using Dynamic SQL Statements in Stored Procedures, Part 2

By Scott Mitchell

  • Read Part 1

  • In Part 1 we looked at using the EXEC statement to execute dynamic SQL statements in stored procedures. However, we hadn't looked at a practical example of generating a SQL statement based on a user's input. In this part we'll complete our study of dynamic SQL in stored procedures!

    - continued -

    Now, imagine that instead of hard-coding mitchell, we want to base it on user input. Well, this isn't too difficult. First, we need to pass in a parameter into our stored procedure. Since the LastName column in our Employee table would be a character-type, we'll pass in a varchar(100). All we need to do is alter the first line of the stored procedure:

    CREATE PROCEDURE sp_MyFirstDynamicSP ( @LastName varchar(100) )

    (For information on creating stored procedures with parameter lists, see: Writing a Stored Procedure.) Now we need to alter our dynamic SQL statement to replace the hard-coded value of mitchell with the value of the @LastName variable.

    CREATE PROCEDURE sp_MyFirstDynamicSP ( @LastName varchar(100) )
        EXEC('SELECT FirstName, LastName, SSN, Salary
              FROM Employee
              WHERE LastName LIKE ''%' + @LastName + '%'' ')

    Note that the + character is the concatenation character for SQL. Now, to call this stored procedure from an ASP page, instead of having our dynamic SQL string that we looked at the beginning of this article, we'd simply have a SQL statement that executed the stored procedure and passed in the user-entered last name they wished to search on:

    Dim strSQL
    strSQL = "sp_MyFirstDynamicSP '" & Request("LastName") & "'"

    That's all there is to it! If you are not very familiar with stored procedures, or how they are called from an ASP page, please do read Nathan Pond's great article Writing a Stored Procedure. It should answer all of your questions!

    One word of caution, before I leave you. If you want to base a dynamic query on, say, an integer value (for example, you want to return rows where some integer column equals some user-entered value, you can do it in one of two ways:

      1.) From the ASP page pass in a string whose value is the integer to search for. So, you might do:

      Dim strSQL
      strSQL = "sp_MyFirstDynamicSP '" & CStr(Request("EmployeeID")) & "'"

      and then in the stored procedure, your input parameter would be of a character type (say varchar(5)) and would be coded like:

      CREATE PROCEDURE sp_MyFirstDynamicSP ( @EmployeeID varchar(5) )
          EXEC('SELECT FirstName, LastName, SSN
                FROM Employee
                WHERE EmployeeID = ' + @EmployeeID)

      2.) From the ASP page, pass in an integer. To pass a numeric value into a stored procedure, simply leave off the apostrophes surrounding the value:

      Dim strSQL
      strSQL = "sp_MyFirstDynamicSP " & CInt(Request("EmployeeID"))

      Note that there are no apostrophe's surrounding the value of the user-entered EmployeeID. In the stored procedure, things become a bit more complex. First, accept the incoming variable as an int. Second, since you are trying to insert an integer value into a string, you will need to convert the integer into a character. Use the T-SQL CONVERT function to do this:

      CREATE PROCEDURE sp_MyFirstDynamicSP ( @EmployeeID int )
          EXEC('SELECT FirstName, LastName, SSN
                FROM Employee
                WHERE EmployeeID = ' + CONVERT(varchar(5), @EmployeeID) )

      Note that @EmployeeID is being passed in as an int, and that I use the CONVERT function to turn @EmployeeID, an int, into a varchar(5). If you do not explicitly convert EmployeeID to a character-type, you will get an error when you try to save the stored procedure.

    In Part 3 we'll look at some other ways you can use dynamic SQL strings to increase the power of your SQL queries!

  • Read Part 3!

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article