Contact Us
Site Map

Search

---

Why use stored procedures?

---

1. Performance - stored procedures are pre-compiled by SQL Server, so performance is fast. The increase in speed is similar to the benefits of porting lengthy blocks of resource-intensive ASP code into a COM/COM+ DLL (without the overhead implications). 
    
   
2. Isolation of Logic - stored procedures allow you to separate your data tier logic from presentation code. This way, your ASP experts don't have to be SQL experts - and vice-versa. The SQL gurus can often change the way a stored procedure works with data, without even having to touch the ASP gurus' code. Granted, there are some times when this can add complexity to a project; for those situations, I recommend using communication and SourceSafe, and remember #1.

---

When should I use stored procedures?

---

Our company policy is to encapsulate ALL data logic into stored procedures, and eliminate all direct SQL calls (whether from ISAPI, COM, ASP, Servlets, or Java). For quick and dirty tests, you don't need to be 100% formal about this. But for anything in production — if you care about performance — you should be using stored procedures as much as possible.

---

How do I write stored procedures?

---

Stored procedures are created within SQL Server's Enterprise Manager GUI. You can create them from Query Analyzer, or even from ASP code. Personally, I prefer the GUI for its "Check Syntax" button, but many people think this is gratuitous and also find the space in the procedure GUI limiting. In any case, before we get into the process, let's show a simple example of a stored procedure. 
 
Note that your instinct may be to name a stored procedure with the sp_ prefix. Please do not do this, as this prefix is reserved for system stored procedures. In addition to making your procedures confusing and possibly opening the doors for pre-empting critical procedures used elsewhere in your code; even if you choose a unique name, this causes a performance hit as the engine checks for that procedure name in the Master database first. Accordingly, the procedures demonstrated in this article will be given the prefix FAQ_. 
 


CREATE PROCEDURE dbo.FAQ_GetAuthors AS  BEGIN      SELECT lastname,firstname          FROM authors          ORDER BY lastname  END

 
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 stored procedure is the CREATE PROCEDURE wrapper (and the BEGIN/END statements, which aren't altogether necessary). 
 
You can call this from ASP as follows: 
 


<%      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      set rs = conn.execute("EXEC dbo.FAQ_GetAuthors")      do while not rs.eof          lastname = rs(0)          firstname = rs(1)          response.write lastname & ", " & firstname & "<br>"          rs.movenext      loop      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

 
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. 

 

• What should my connection string look like? 
   
  
  To force TCP/IP over named pipes, and to avoid superfluous layers, here is a fictitious example of the kind of connection string I always use: 
       
  

  <%      cs = "provider=SQLOLEDB; network=DBMSSOCN; server=1.1.1.1;"      ' if you use a port other than 1433, use server=1.1.1.1,1500;      ' (where 1500 is the port number SQL Server is listening on)      cs = cs & "database=pubs; uid=myUsername; pwd=myPassword;"      set conn = CreateObject("ADODB.Connection")      conn.open cs      ' ...  %>

   
  For more information on connection strings, see Article #2126.
 

• Why didn't I use a command object? 
   
  
  The command object brings overhead that is rarely necessary when executing any stored procedure. This object has very stringent guidelines as far as creating parameters (including strictly matching any and all data types being passed), and almost always forces you to include ADOVBS.inc for constants (which is inefficient - see Article #2112). Some texts will claim "it's faster, and simpler to use." While the former might be true in the case of a single output parameter and no records, I've seen no proof of it otherwise... and I outright disagree with the latter. Command object code is monstrous and very error-prone.
 

• Why didn't I use a recordset object? 
   
  
  I avoid ADODB.Recordset for pretty much for the same reasons as avoiding ADODB.Command. It has all kinds of overhead I very rarely require, and uses more complex code to accomplish the same results. For more information, see Article #2191 (and recall that, in earlier versions of MDAC, an RS.Open call would tend to crash if it was an empty resultset).
 

• Why did I use rs(0) instead of rs("lastname")? 
   
  
  While retrieving recordset elements by name is more intuitive, it is actually much less efficient than retrieving them by index number. Also, this almost forces you to assign them to local variables, which aids in reuse and in retrieving text/memo columns. This, however, is one of the few scenarios where you have to be in tune with the stored procedure developers... if they change the SELECT column order, they have to tell you. When you work alone, this danger isn't as great... but it might be one of those preference issues where readability outweighs performance.
 
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: 
 


Column Name	Data Type		Column Name	Data Type
id	INT IDENTITY		synopsis	VARCHAR (4000)
title	VARCHAR (255)		inprint	BIT (DEFAULT=1)
pubdate	DATETIME		salesCount	INT

 
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: 
 


CREATE TABLE BOOKS  (      id INT IDENTITY,      title VARCHAR(255) NOT NULL DEFAULT '',      pubdate DATETIME NOT NULL DEFAULT '19000101',      synopsis VARCHAR(4000) NOT NULL DEFAULT '',      inprint BIT NOT NULL DEFAULT 1,      salesCount INT NOT NULL DEFAULT 0  )  GO    GRANT ALL ON BOOKS TO myUsername  GO

 
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

---

Now that we have a table created, we can start writing stored procedures around it. The first one we'll need, of course, is an INSERT statement. After all, we need to get data in there somehow! A typical INSERT statement for this table might be as follows: 
 


INSERT INTO BOOKS  (      title,      pubdate,      synopsis,      salesCount  )  VALUES  (      'Seven Minute Stored Procedures',      '20000101',      'This book helps you trim the fat, and feel good about coding again!',      0  )

 
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 stored procedure 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 stored procedure will be pretty simple. Open Query Analyzer, copy the following code, and hit F5: 
 


CREATE PROCEDURE dbo.FAQ_InsertBook      @title VARCHAR(255),      @pubdate DATETIME,      @synopsis VARCHAR(4000),      @salesCount INT  AS  BEGIN      SET NOCOUNT ON      DECLARE @newBookID INT      INSERT BOOKS      (          title,          pubdate,          synopsis,          salesCount      )      VALUES      (          @title,          @pubdate,          @synopsis,          @salesCount      )      SELECT @newBookID = @@IDENTITY      SELECT newBookID = @newBookID  END  GO    GRANT EXEC ON dbo.FAQ_InsertBook to myUsername  GO

 
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 stored procedures with dbo. This will prevent permissions issues later on, and will prevent the awkward situation where two people, connected as two different users, create stored procedures 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 stored procedures, 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 stored procedures. 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: 
 


INSERT ...      DELETE ...      SELECT ...

 
When I would call this stored procedure 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: 
 


SET NOCOUNT ON      INSERT ...      DELETE ...      SELECT ...

 
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 stored procedure.) This value will store the IDENTITY number for the record inserted by the stored procedure. 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 procedure 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 stored procedure before we move to ASP. In Query Analyzer, copy the following query: 
         


EXEC dbo.FAQ_InsertBook      @title='Seven Minute Stored Procedures',      @pubdate='20000101',      @synopsis='This book helps you trim the fat, and feel good about coding again!',      @salesCount=0

 
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. 
 


<%  FUNCTION FixDate(str)      datestr = cdate(str)      mStr = month(datestr): dStr = day(datestr): yStr = year(datestr)      if (Clng(mStr) < 10 and len(mStr)=1) then mStr = "0" & mStr      if (Clng(dStr) < 10 and len(dStr)=1) then dStr = "0" & dStr      FixDate = yStr & mStr & dStr  END FUNCTION    FUNCTION FixString(str)      FixString = replace(str,"'","''")  END FUNCTION        title = "Six Minute Stored Procedures"      pubdate = "20000101"      synopsis = "This one is better than the last one."      salesCount = 0         title = FixString(title)      pubdate = FixDate(pubdate)      synopsis = FixString(synopsis)      salesCount = cLng(salesCount)         sql = "EXEC dbo.FAQ_InsertBook" &_          " @title='" & title & "'," &_          " @pubdate='" & pubdate & "'," &_          " @synopsis='" & synopsis & "'," &_          " @salesCount=" & salesCount         set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      set rs = conn.execute(sql)      Response.write("Record inserted. ID number was <b>" & rs(0) & "</b>.")      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

 
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

---

Okay, so now we have data in there, what if we want to update it? Let's say the book 'Seven Minute Stored Procedures' (ID = 1) reached a sales count of 4,023 units, and was shortly thereafter discontinued. We would start with the same principle -- a basic UPDATE statement in T-SQL might look as follows: 
 


UPDATE BOOKS SET      title = 'Seven Minute Stored Procedures',      pubdate = '20000101',      synopsis = 'This book helps you trim the fat, and feel good about coding again!',      salesCount = 4023,      inprint = 0  WHERE      id = 1

 
A stored procedure 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 procedure, 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: 
 


CREATE PROCEDURE dbo.FAQ_UpdateBook       @id INT,       @title VARCHAR(255),       @pubdate DATETIME,       @synopsis VARCHAR(4000),       @salesCount INT,       @inprint BIT = 1   AS      BEGIN           UPDATE BOOKS SET               title = @title,               pubdate = @pubdate,               synopsis = @synopsis,               salesCount = @salesCount,               inprint = @inprint           WHERE               id = @id       END  GO    GRANT EXEC ON dbo.FAQ_UpdateBook to myUsername   GO

 
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 stored procedure by issuing the following command to Query Analyzer: 
 


EXEC dbo.FAQ_UpdateBook      @id = 1,      @title = 'Seven Minute Stored Procedures',      @pubdate = '20000101',      @synopsis = 'This book helps you trim the fat, and feel good about coding again!',      @salesCount = 4023,      @inprint = 0

 
And from ASP, the code for this new stored procedure 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: 
 


<!--#INCLUDE FILE='sqlFunctions.asp'-->  <%      id = 1      title = "Seven Minute Stored Procedures"      pubdate = "20000101"      synopsis = "This book was removed from circulation effective 6/1/2000."      salesCount = 4023      inprint = 0         id = cLng(id)      title = FixString(title)      pubdate = FixDate(pubdate)      synopsis = FixString(synopsis)      salesCount = formatnumber(cLng(salesCount),0,-1,-1,0)      inprint = cLng(inprint)         sql = "EXEC dbo.FAQ_UpdateBook" &_          " @id=" & id & "," &_          " @title='" & title & "'," &_          " @pubdate='" & pubdate & "'," &_          " @synopsis='" & synopsis & "'," &_          " @salesCount=" & salesCount & "," &_          " @inprint=" & inprint       set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      conn.execute sql, , 129      Response.write("Record updated.")      conn.close: set conn = nothing  %>

 
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 stored procedure 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

---

Now we'll get into a slightly more complex example. Let's say we want to delete all records where the word "seven" appears in the title (hey, I didn't say these examples had to be logical!). The typical DELETE statement would look like this: 
 


DELETE FROM BOOKS      WHERE      title LIKE '%seven%'

 
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 stored procedure. 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 stored procedure looks like. It is actually quite similar to the previous stored procedures, with far fewer parameters being passed: 
     


CREATE PROCEDURE dbo.FAQ_DeleteBook       @criteria VARCHAR(255)   AS  BEGIN       SET NOCOUNT ON      DELETE BOOKS           WHERE          SUBSTRING(LOWER(@criteria),LOWER(title))>0      SELECT rc = @@ROWCOUNT   END   GO    GRANT EXEC ON dbo.FAQ_DeleteBook to myUsername   GO

 
Executing this query from Query Analyzer would look like this: 
 


EXEC dbo.FAQ_DeleteBook @title='seven'

 
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 stored procedure is working as advertised, we can move to ASP. 
 


<!--#INCLUDE FILE='sqlFunctions.asp'-->  <%      title = "seven"         sql = "EXEC dbo.FAQ_DeleteBook @title='" & FixString(title) & "'"      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      set rs = conn.execute(sql)      Response.write(rs(0) & " rows deleted.")      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

 
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 stored procedures.

---

SELECT

---

A typical SELECT procedure (e.g. to present the properties of a single book, in an edit form on an ASP page) might look like this: 
 


CREATE PROCEDURE dbo.FAQ_getBookDetails      @id INT = NULL  AS  BEGIN      SET NOCOUNT ON        SELECT          id,          title,          pubdate,          synopsis,          salesCount,          inprint      FROM          Books      WHERE          id = COALESCE(id, @id)  END  GO    GRANT EXEC ON dbo.FAQ_getBookDetails TO myUsername   GO

 
Note that the @id parameter is optional; this stored procedure 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: 
 


<!--#INCLUDE FILE='sqlFunctions.asp'-->  <%      id = 1        sql = "EXEC dbo.FAQ_getBookDetails @id = " & id      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      set rs = conn.execute(sql)      for i = 0 to 5          Response.Write rs(i) & "