working with Stored Procedures

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

In this article, I'll discuss how to create and execute stored procedures and views using VS.NET. For most of the examples in this article, I'll use SQL Server's copy of Northwind as the sample procedures, which are used to group multiple SQL statements to perform complex activities associated with transaction, security, and so on. Microsoft SQL Server has a variety of stored procedures, including system and extended stored procedures that you can read about in its Books Online help, for example, but I'll talk about user-defined stored procedures in this article.)

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

Conclusion

Hope this article would have helped you in understanding working with Stored Procedures. See other articles on the website also for further reference.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.