Programming ASP.NET, Second Edition

Stored Procedures

Until now, you’ve interacted with the database using nothing but SQL statements. Many real-world applications interacting with SQL Server or other large databases will use stored procedures. Stored procedures can be compiled by the database, and, thus, offer better performance.

The easiest way to create a stored procedure (often referred to as a sproc ) is to begin with a working SQL statement. If you return to Example 11-6, you will find two SQL Select statements. The first is in the CreateBugDataSet method:

StringBuilder s = new StringBuilder(
   "Select b.BugID, h.BugHistoryID, b.Description,h.Response, ");
s.Append("o.FullName as owner, ");
s.Append("p.ProductDescription, ");
s.Append("r.FullName as reporter, ");
s.Append("s.StatusDescription, ");
s.Append("sev.SeverityDescription, ");
s.Append("h.DateStamp ");
s.Append("from  ");
s.Append("(select bugID, max(bugHistoryID) as maxHistoryID ");
s.Append("from BugHistory group by bugID) t ");
s.Append("join bugs b on b.bugid = t.bugid ");
s.Append("join BugHistory h on h.bugHistoryID = t.maxHistoryID ");
s.Append("join lkProduct p on b.Product = p.ProductID  ");
s.Append("join People r on b.Reporter = r.PersonID  ");
s.Append("join People o on h.Owner = o.PersonID ");
s.Append("join lkStatus s on s.statusid = h.status ");
s.Append("join lkSeverity sev on sev.SeverityID = h.severity ");

If you extract the SQL from this statement and insert it into the SQL Server Query analyzer, you can run that statement and get back ...