Creating a Stored Procedure
There are different ways you can create stored procedures. As a database
administrator, you can use a database server to create and manage stored
procedures. As a programmer, you can create stored procedures programmatically
by using the CREATE PROCEDURE SQL statement. You can also create and manage
stored procedure using server explorer in VS.NET. In this article, you'll see
how to create and manage stored procedures using VS.NET.
In VS.NET, the Server Explorer enables you to create, update, and delete stored
procedures. You can launch the Server Explorer by selecting View > Server
Explorer (see figure 10-1).
Figure 10-1. Launching the Server Explorer from the View menu
As you can see from figure 10-2, you can expand a database's Stored Procedures
node to manage stored procedures. You can view stored procedures by
double-clicking on the stored procedure's name.
Figure 10-2. Viewing available stored procedures in Northwind database
The right-click menu option allows you to create a new stored procedure and
edit, delete, and execute existing stored procedures (see figure 10-3).
Figure 10-3. Creating, editing, deleting, and running stored procedures
The Edit Stored Procedures option lets you edit a stored procedure. The stored
procedure editor looks like figure 10-4.
Figure 10-4. Stored procedure editor
You can create a new stored procedure by using the new stored procedure menu
option after right-clicking the Stored Procedures node (see Figure 10-5).
Figure 10-5. Creating a new stored procedure from the Server Explorer
The New Stored Procedure menu option launches the stored procedure editor, which
is used to write stored procedures.
As you can see from figure 10-6, the stored procedure editor lets you edit the
stored procedure. In SQL Server, dbo is for the database owner. In figure 10-6,
the syntax CREATE PROCEDURE dbo.StoredProcedure1 creates a stored procedure
where StoredProcedure1 is the name of the stored procedure and dbo is the owner
of the stored procedure.
Figure 10-6. The stored procedure editor
A stored procedure can return data as a result of a SELECT statement, a return
code (an integer value), or an output parameter. As you can see from figure
10-6, the article after CREATE PROCEDURE closed with comments (/* and */) is the
parameters section. If you have no requirement of using parameters in a stored
procedure, you can skip this area. The section after AS is the actual SQL
statement, and the section after RETURN is to return a value when you execute a
stored procedure.
OK, now you can write a simple SELECT statement and save it as a stored
procedure. Use this SQL statement:
SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE Country = 'USA'
To select three columns values for the customers from the United States. I
changed the stored procedure name to mySP. The final stored procedure looks like
figure 10-7.
Figure 10-7. The mySP stored procedure in the editor
You can save a stored procedure by using FILE > Save mySP or the Save All menu
option or toolbar button. The save option is also available on the right-click
menu on the stored procedure editor (see Figure 10-8).
Figure 10-8. Saving a stored procedure
Note: The save option not only creates a stored procedure, but it also
changes the CREATE PROCEDURE statement to ALTER PROCEDURE because the stored
procedure is already created.
Now, if you go to the Server Explorer and see all the stored procedure for the
Northwind database, you'll see your stored procedure listed. As you can see from
figure 10-9, stored procedure mySP has three fields listed under it that appear
in the SELECT statement.
Figure 10-9. Stored procedures list for the Northwind database
Executing a Stored Procedure from VS .NET
As you've seen in figure 10-3, you can execute a stored procedure by right
clicking on the stored procedure and selecting the run Stored Procedure option.
You can also run a stored procedure by right-clicking on the stored procedure
editor and selecting Run Stored Procedure.
The output of stored procedure mySP looks like figure 10-10.
Figure 10-10. The output of mySP stored procedure in VS .NET
Note: Similar to your SELECT SQL statement in the stored procedure, you
can use any SQL statement, such as UPDATE and DELETE.
A stored procedure can also accept input parameters. For example, if you view
the CustomersDetail stored procedure (See Figure 10-11), which takes a value of
parameter of OrderId based on the OrderID value, it returns ProcedureName and
calculates Discount and ExtendedPrice.
Figure 10-11. Stored procedure with input parameter
When you run the stored procedure it asks you the value for OrderID parameter,
as shown in Figure 10-12.
Figure 10-12. Stored procedure parameter
The output of the stored procedure CustOrdersDetail looks like figure 10-13.
Figure 10-13. The output of stored procedure CustOrdersDetail