Introduction
This 3 article series will cover inserting data into SQL Server, without the help of Visual Studio’s drag and drop controls and configuration wizards. A special emphasis will be placed on its application within a tiered environment.
Part 1 – Part 2 – Part 3
Part 1 – Creating a Stored Procedure
While inline SQL may be an easy way to access databases, it’s far from ideal. Stored procedures are the recommended method of SQL Server access. They protect you from SQL Injection attacks (If you don’t know anything about this, I suggest you do some googling, it’ll scare you straight), are precompiled to allow for faster response times and are an easy way to check your SQL.
There are many ways to create stored procedures. Query Analyzer, SQL Server Enterprise Manager, or you can even create them from within Visual Studio if you wish. I prefer Query Analyzer, but the main approach is the same. For the purpose of this article, I’ll be using the Northwind Database and will be inserting into the Customer table.
Creating a new Stored Procedure
Below, you’ll see a bare-bones stored procedure. In fact, it doesn’t do anything right now. This is what you’ll see when you create a new stored procedure. Notice the CREATE PROCEDURE statement. This defines the name of your stored procedure which I’ve set as insert_customer. Now that you have a blank stored procedure, we need to talk about parameters.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE insert_customer
AS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Parameters in Stored Procedures
Parameters can be input, output or both. They are the only way of feeding information to a stored procedure, although not the only method of returning information (I will discuss some of these later). As you can see in the modified procedure below, the parameter section is directly below the declaration, but before the body. I’ve defined one parameter for each field in the Customer table. You will also see one parameter labeled as OUTPUT. This will be used within the stored procedure to return the primary key of the inserted record. Parameters not provided with a direction are assumed to be input. Now that we’ve defined the interface to our stored procedure, we need to make it do something!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE insert_customer
@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),
@Key INT OUTPUT
AS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The Insert statement
We’ve added a simple insert statement to the stored procedure using our input parameters as the values to insert. Although we’ve only placed the insert statement into the body of our stored procedure, you can perform any T-SQL operation within the body of the procedure – define variables, run queries, delete/insert/update records, etc. However, you might notice that we have not returned the primary key of the new customer record. I’ll cover that in the next section.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE insert_customer
@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),
@Key INT OUTPUT
AS
INSERT INTO Customers
(
CompanyName, ContactName, ContactTitle,
Address, City, Region,
PostalCode, Country, Phone,
Fax
)
VALUES
(
@CompanyName, @ContactName, @ContactTitle,
@Address, @City, @Region,
@PostalCode, @Country, @Phone,
@Fax
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Returning Values
As I mentioned earlier, there are a couple of ways to return values from a stored procedure. The method that I’m using for the purpose of this article is an output parameter. For integer values, a RETURN statement could also be used, and would work well in this situation. Additionally, it is possible to simply include a SELECT statement in a stored procedure and retrieve the selected rows from within your application.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE insert_customer
@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),
@Key INT OUTPUT
AS
INSERT INTO Customers
(
CompanyName, ContactName, ContactTitle,
Address, City, Region,
PostalCode, Country, Phone,
Fax
)
VALUES
(
@CompanyName, @ContactName, @ContactTitle,
@Address, @City, @Region,
@PostalCode, @Country, @Phone,
@Fax
)
SET @Key = @@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
It may seem like an awful lot of work to create a stored procedure for every read/insert/update/delete to your database. However, in a large application, you’ll find that it’s almost essential. Stored procedures are much easier to read than SQL embedded into VS, and as I mentioned, they help in protecting you from SQL Injection attacks. The feature that I like the best is that it provides you with an easy way of checking your SQL syntax. There’s no need to run your application to see if you made any mistakes in your SQL.
Now that our stored procedure is complete, it’s ready to use in an application. But first we’ll need to create a connection to the database containing the stored procedure - Part 2.