|
|
8000XXXX Errors ASP.NET 2.0 Classic ASP 1.0 Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search | ASP FAQ Tutorials :: Databases :: Other Articles :: Using Stored Procedures 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. INSERT
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. UPDATE
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). DELETE
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 . SELECT
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:
|