Create Stored Procedure for accessing data from database in ASP.NET, Example

In my previous post we have discussed about how to design Department table for shopping cart. Now, we will learn, How to create stored procedure for retrieving  information from database table. You need to create the GetDepartments stored procedure, which returns department information from the Department table. This stored procedure is part of the data tier and will be accessed from the business tier. The final goal is to have this data displayed in the user control.

The SQL code that retrieves the necessary data and that you need to save to the database as the GetDepartments stored procedure is the following:

Select DepartmentID, Name, Description From Department

This command returns all the department information

Note: unless you have a specific reason to do so, never ask for all columns (using the * wildcard) when you only need a part of them. This generate more traffic and stress on the database server then necessary and slows down performance. Moreover, even if you do need to ask for all columns in the table, it's safer to mention them explicitly to protect your application in case the number of order of columns changes in future.

Saving the Query As a Stored Procedure

As with data tables, after you know the structure, implementing the stored procedure is a piece of cake. Now that you know the SQL code, the tools will help you save the query as a stored procedure easily.

The Syntax for creating a stored procedure that has no input or output parameters is as follows:

CREATE PROCEDURE <procedure name>
AS
<stored procedure code>

if the procedure already exists and you just want to update its code, use alter PROCEDURE instead of above. Stored procedures can have input or output parameters. Because GetDepartments doesn't have any parameters, you don't have to bother about them right now.

Lets take a simple example of writing the Stored Procedure

Step-1: Make sure the data connection to the database is expanded and selected in server explorer. Choose data->Add New--> Stored Procedure. Alternatively, you can right-click the Stored Procedure node in server explorer and select Add New Stored Procedure.

Step-2: Replace the default text with GetDepartment's stored procedure

CREATE PROCEDURE GetDepartments 

AS
SELECT DepartmentID, Name, Description
From Department

Step-3: Press Ctrl+S to save the stored procedure. Unlike with the tables, you won't be asked for a name because the database already knows that you're talking about the GetDepartment's stored procedure

Note: Saving the Stored Procedure actually executes the SQL code you entered, which creates the stored procedure in the database, after saving the procedure, the CREATE keyword becomes ALTER, which is the SQL command that changes the code of an existing procedure.

Step-4: Now test your first stored procedure to see that it's actually working. Navigate to the GetDepartments stored procedure node in server explorer and select execute.

Step-5: After running the stored procedure, you can see the results in the output window.