Application Security Testing: An Integral Part of DevOps
Because stored procedures are not written in Visual Basic .NET, VB.NET programmers have to learn stored procedure grammar and keywords and practice writing SQL to use them. In a perfect world, the division of labor in software development would be divided between a stored procedure programmer and a Visual Basic .NET programmer so neither would have to know the ins and outs of the other's work. However, dividing these jobs in a team environment carries the risk of one programmer inadvertently dropping or modifying the other's precious stored procedure, which can wreak havoc on the team's code.
This article assumes that you are either an aspiring SQL programmer who dabbles in Visual Basic .NET or a Visual Basic .NET programmer who has to write his own stored procedures for MS SQL Server. It demonstrates some basics of writing SQL stored procedures—SQL is a whole language, so you will need to pick up a couple books, some courses, and hundreds of hours of independent study if you want to become an expert—and shows you how to use those stored procedures from Visual Basic .NET code. The reason for doing this is simple: Isolating SQL code from VB.NET code to clearly create divisions of labor and focus for your solution.
This article uses VS.NET 2003 and the desktop edition of SQL Server (called MSDE), but its code listings should work in most recent editions of SQL and all versions of VS.NET.
A Quick Primer for Writing SQL Server Stored Procedures
The language that supports stored procedures for Microsoft SQL Server is called Transact SQL or T-SQL. Getting into the differences among all of the implementations of ANSI SQL won't help here. The important thing to know is that almost every implementation deviates from standard SQL, making it a separate language just like Visual Basic .NET is different from QuickBasic, VB6, GW-BASIC, and RomBasic. You have to master only as much SQL in your implementation as you need to get the job done.
The key to writing great SQL is mastering the language. The key to writing functional SQL is pretty straightforward: Basically, write a plain vanilla SQL statement and add a procedure header. That's all there is to it.
Plain SQL with a procedure header is functional because you can write all of your validation rules, lookups, and constraint logic in Visual Basic .NET. You don't need SQL to do this. On the flipside, if you write plain vanilla stored procedures, you leave your database wide open for corruption, which is the counter argument for adding constraints in your code and mastering SQL.
Writing a stored procedure
Writing a stored procedure is easy. The IDE will generate a procedure stub for you; all you need to do is provide a name, add desired parameters, and define the T-SQL.
For demonstration, use the Northwind database. If you have almost any version of MS SQL Server installed, you should have a copy of the Northwind database too. (If you don't have the Northwind database, use the definitions in the INSERT procedure you will write and create a database with a Customers table using the same schema.)
Follow these steps to create a stored procedure:
- Run Visual Studio .NET.
- Create a new VB.NET Console Application project.
- Open the Server Explorer by selecting View|Server Explorer. (You may not have this option on academic or standard versions of VS.NET. If you'd prefer, you can use MS SQL Enterprise Manager, Query Analyzer, the command line tool osql.exe, or work with existing stored procedures, and skip to the next part.)
- In the Server Explorer, expand the server tab, expand the SQL Servers tab, and expand the server instance with which you will be working. (If you don't see any server instance, right-click the SQL Servers node of the server explorer and select Register SQL Server Instance.) Your Server Explorer window should look something like Figure 1, although your server instance name will be different than the one the figure shows.
- Expand the Northwind node, followed by the Stored Procedures node.
- Right-click on the Stored Procedures node and select New Stored Procedure. (Alternately, you can select the Database|New Stored Procedure menu item in VS.NET.)
Figure 1: The Server Explorer After Step 4, Opening a SQL Server Instance.
Step 6 creates the stored procedure stub you'll see in Listing 1. (The last digit of the stored procedure name will be incremented by 1 if you save the stored procedure with the default stub name and generate subsequent stored procedures.)
Listing 1: Generating a Stored Procedure Stub
CREATE PROCEDURE dbo.StoredProcedure1 /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS /* SET NOCOUNT ON */ RETURN
The stubbed procedure also opened in the stored procedure designer, an editor with its own context menus that are primarily designed to code, run, and test T-SQL. All you need to do at this point is change the name, add parameters, and add the T-SQL after the AS keyword. For example, to write an InsertCustomers procedure, you list the parameters and types from the customers table and write an INSERT statement. The result looks like the code in Listing 2.
Listing 2: Writing an INSERT Stored Procedure in T-SQL
CREATE PROCEDURE dbo.InsertCustomer ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24) ) AS INSERT INTO Customers ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax ) VALUES ( @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax )
Insert statements don't vary much. Generally, you write INSERT INTO tablename, list the field names in order, and follow those with the values (one for each field). You can copy the parameter names, types, and sizes for the procedure header from the table designer right into the SQL designer.
To quickly copy the Customers' table schema, do this:
- In the Server Explorer, expand the Northwind Tables node.
- Select the Customers table.
- Select Design Table from the context or Database menu.
- Click the upper-left most cell of the Table designer to select all columns and rows—think of the Table designer as a database table itself—and press Ctrl+C.
- Switch to the stored procedure designer (or editor, if you like) and press Ctrl+V (in other words, copy and paste the schema).
- Add parentheses around the size values and delimit each parameter—except the last one—with a comma.
That's it. You have written a basic but functional stored procedure.