Where can I get basic info about using stored procedures?
In this article, I will step through some INSERT, UPDATE and DELETE, from the ground up. We will explore a few speed bumps that I have experienced, and which you will most likely experience also.
This article assumes you're familiar with SQL Server and ASP, and have dbo-level privileges in Enterprise Manager. Please be sure you have the latest version of Microsoft Data Access Components (MDAC) installed. If you're running NT 4.0 or Windows 9x, you can download new versions at MDAC Download Page. See Article #2057 to determine which version you're currently running.
These samples were tested using the version of MDAC that ships with Windows 2000 (MDAC 2.5), as well as the version that is installed by SQL Server 2000 (MDAC 2.6), Windows Server 2003 (MDAC 2.7), and finally the most recent update, MDAC 2.8.
Why use ?
When should I use ?
How do I write ?
Note that your instinct may be to name a with the sp_ prefix. Please do not do this, as this prefix is reserved for system . In addition to making your confusing and possibly opening the doors for pre-empting critical used elsewhere in your code; even if you choose a unique name, this causes a performance hit as the engine checks for that name in the Master database first. Accordingly, the demonstrated in this article will be given the prefix FAQ_.
This simply does a SELECT statement, returning authors' last and first names, ordered alphabetically by last name. Notice the only syntactical difference between a typical SELECT statement and an equivalent is the CREATE wrapper (and the BEGIN/END statements, which aren't altogether necessary).
You can call this from ASP as follows:
This was a fairly trivial example; I'd like to go much deeper than this today. But first, let's examine some questions you probably have about the above code.
Let's move on to a more concrete example. We'll start with a fairly simple table. I created a table in pubs called BOOKS. The columns are shown in the following table:
I used one column to represent each datatype (and/or property) that I will be covering in this article. DATETIME and VARCHAR(>255), in particular, seem to cause many problems for ASP developers. Here is the script used to create the table from Query Analyzer, so you don't have to muck with the GUI:
We make the id column an IDENTITY value, so that it can be a unique, auto-incrementing value. Replace "myUsername" with the true username you'll be using to connect with - the last line applies appropriate permissions (it should have INSERT, SELECT, UPDATE and DELETE). Depending on the version of SQL Server you're using, you may have to hit F5 before you see the new table in the Tables list.
One of the problems with running an INSERT statement directly is that you need to find some other means of retrieving the IDENTITY value of the record you inserted. This is one of the most common questions asked in Microsoft's public newsgroups, and it seems to cause people a lot of trouble (both in initially doing it, and - in concurrent user scenarios - making sure that they are getting the proper IDENTITY value back, not someone else's). A makes this much more reliable, though admittedly not perfect. @@IDENTITY is a *global* variable SQL Server uses to keep track of the IDENTITY value last inserted. While I have never personally experienced a mistaken identity, I'm not going to go out on a limb and say it will never happen.
[Note: if you are running SQL Server 2000, replace all further instances of @@IDENTITY with SCOPE_IDENTITY(). This is a new method which makes sure you only get the IDENTITY value that *your* connection generated, and that wasn't generated by a trigger that happened *after* the insert you care about.]
Changing this statement to a will be pretty simple. Open Query Analyzer, copy the following code, and hit F5:
Again, replace "myUsername" with the name of the account that will be accessing this application.
Let's review our code before we do anything with it. Always prefix with dbo. This will prevent permissions issues later on, and will prevent the awkward situation where two people, connected as two different users, create with the same name (yes, this is possible). We take in four parameters: @title, @pubdate, @synopsis, and @salesCount. Even though the datatype for @pubdate is DATETIME, I've found that I run across far fewer problems when I avoid passing explicit datetime values directly to , and rather pass in CHAR representations in YYYYMMDD format.[Footnote 1]
After the BEGIN command, we SET NOCOUNT ON -- this prevents interim statements from being returned as recordsets.
I strongly recommend you always use SET NOCOUNT ON at the beginning of your . This is because, as you might learn soon enough, statements that return RowsAffected results (or any message, for that matter) in Query Analyzer can also be interpreted as recordsets in client code -- which means the recordset you think you're on isn't necessarily the right one. One example that dogged me for a few hours went as follows:
When I would call this from an ActiveX DLL, I would try and obtain some columns from the recordset... only to receive the "ordinal name not found" error. It took me a while to realize that using NextRecordset() would eventually get me to the results I was after, but I didn't like this at all (the code was inefficient, because it was returning much more data than it needed to be). Once I changed it to the following, everything was fine:
I have quickly become accustomed to wrapping non-result-returning code inside of SET NOCOUNT ON... to avoid being bitten by this in the future. So, if you decide not to use the NOCOUNT options, at least (having read this) you'll know what to look for if you experience similar problems.
Next we use the DECLARE command to create a temporary INT value called @newBookID. (While we could do this without a temporary variable, this is a good habit to have - you will eventually be dealing with multiple IDENTITY values in one .) This value will store the IDENTITY number for the record inserted by the . The next 14 lines make up the INSERT statement, utilizing the four parameters passed to the query — but otherwise looking like most INSERT statements you've seen before. I have a habit of indenting code nicely so that it is easy to read. A few of my co-workers think this is a waste of time, and it is very frustrating when I have to modify / troubleshoot their code.
Note that we don't have to pass a value for the inprint column, since it has a default value of 1 — and we are assuming that all books are, in fact, in print (we'll deal with overriding this value shortly).
The first SELECT statement in this applies the global @@IDENTITY value (the latest insert) to the local variable @newBookID. The final SELECT statement returns the value to the client.
All right, let's test this before we move to ASP. In Query Analyzer, copy the following query:
When you execute the query, you should see the number 1 under the newBookID column in the results pane. Next you'll see how we return this value, within a recordset, to our ASP page.[Footnote 2]
The goal here, of course, is to have ASP scripts running these things — not to be fiddling with Query Analyzer and Enterprise Manager all day long.
We'll start script similar to the ASP code above. I will populate local variables with values, but you can assume that these values come from anywhere (e.g. the Form or QueryString collection). I have also included my "FixDate" and "FixString" functions, which clean up dates and escape apostrophes, respectively.
If you've gotten this far and are getting errors from the SQL Server driver, a good idea to try and debug is to Response.Write the sql variable, so you can review on-screen EXACTLY what you're passing to the database engine. It is often easier to spot a missing single quote or comma in a resulting string than in concatenated code like the above sample. See Article #2145 for more info.
A that does this kind of update is extremely similar to the one we wrote earlier for inserting a book. Note that typically, in a web application, you're not going to be selecting which columns you update; rather, you present all columns to the user in a form, and pass them all to the update , whether the data was actually modified or not. (You can use triggers to determine which portions of the data were changed; for some examples, see Article #2496.) Here is the code:
The biggest difference here is that we have to pass the ID number as a parameter, so that SQL knows which record we want to apply that change to (without a WHERE clause, it would update every record in the table). Also note that if you DON'T pass a new inprint value, it will not change. The input param @inprint has a default value of 1, so the value in the table will only change if you override it (which we will do in a moment).
You can test this new by issuing the following command to Query Analyzer:
And from ASP, the code for this new would be almost identical to the previous example. Let's assume, from now on, that you have placed the FixDate() and FixString() functions into an #INCLUDE file called sqlFunctions.asp. The following file will update the synopsis of the book to reflect the fact that it is no longer in print:
You're probably asking "what's that 129 for?" This is the constant for adExecuteNoRecords + adCmdText. The adExecuteNoRecords constant (128) tells the SQL engine that you won't be returning or processing any rows, and executes the query more efficiently - providing a bit more performance boost to your application. The adCmdText constant (1) tells the engine what kind of statement you're running, so it doesn't waste cycles guessing. My testing yielded gains better than 20% simply by adding this constant to my execute calls in ASP and VB. Since this didn't return any data, we didn't need to create a recordset object at all; we simply passed the SQL statement directly to the execute method of the connection object. One issue you'll want to watch for is passing larger integer values to SQL statements. Because a large portion of T-SQL syntax relies on commas, you have to be careful not to pass numbers formatted with comma separators (which is why the above salesCount assignment has the additional formatnumber command - in case you're passing to it a value with comma separators).
A common requirement for this kind of query is that it alerts the user how many records were actually deleted. When using a normal DELETE statement, you would have to do a differential between SELECT COUNT(id) before and after the DELETE query is run. This makes for messy and inefficient code, and is not entirely intuitive either. Luckily, SQL Server has another global variable which, like @@IDENTITY, is used to keep track of specific values. This variable, called @@ROWCOUNT, reflects the number of rows affected by a query.
And this brings us to one of the challenges with using @@ROWCOUNT. Any value returned to it is automatically set to 0 when SET NOCOUNT ON is used in conjunction with statements that don't return any rows (such as IF or DELETE statements). Ultimately, this means we're going to have to examine returning multiple recordsets from a . Don't get me wrong, it is not a bad thing to learn. But it is one of the reasons this example is slightly more complex than the previous examples.
Before we get too far, let's see what the looks like. It is actually quite similar to the previous , with far fewer parameters being passed:
Executing this query from Query Analyzer would look like this:
While in a real environment, you should have a primary key (see Article #2504), in this case, you can use FAQ_InsertBook in the Query Analyzer to populate the BOOKS table with a bunch of identical rows, then run FAQ_DeleteBook to make sure the proper number of rows are being deleted. When you are sure the is working as advertised, we can move to ASP.
Note that most DELETE queries would delete by ID number or some other unique identifier; all it takes is someone passing a criteria value of 'a' to delete just about every book in your database. These are just simple examples that try to cover all the little issues that invariably come up when using .
Note that the @id parameter is optional; this can double . However, as your table gets larger, it is doubtful that returning the table in one fell swoop would be useful or desirable. See Article #2120 for some techniques to "page" the data across multiple pages. In any case, to execute the above from ASP and get the details for the book where id = 1:
If we wanted to see *all* books, we might do this:
Of course, you can pretty it up with tables and other HTML tags. You can also improve efficiency by using GetRows() or GetString(); see Article #2467 for more details.
Another thing that happens often is that you want to return multiple resultsets to an ASP page. Let's say we wanted the last three books published, and the three most popular books. Our would look like this:
Yes, that's a horrible name for a . :-)
Now, to deal with multiple resultsets from ASP, we have to use the NextRecordSet() method. So, our ASP code might look like this:
In addition to the new nextRecordSet() call to get to the second set of results, I added if not rs.eof checking to avoid ugly errors when the table is empty.
 One thing you'll want to make sure of is that your ASP pages are actually creating dates in YYYYMMDD format. To be sure my ASP scripts are always using proper CHAR(8) date format no matter who is logged in, I wrote the following VBScript function - which I place in all of my top-level includes (I've also included a simple test to verify that it works):
Of course, you'll have to be careful here. If ASP is returning dates in d/m/yyyy format, your day and month could still be inadvertently transposed.
 Yes, using an output parameter can be slightly more efficient than returning a recordset. However, this is one of those cases where ease of use often outweighs performance concerns, IMHO (output parameters, like ADODB.Command, can be a pain to use). And further to that, I find it more often than not the case that I'm returning multiple recordsets from a (as opposed to the rare case of only one value being returned), whereby you lose any performance advantage of the parameter anyway.
Related ArticlesHow do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?