Create an ASP. NET data storage layer (4)

Use Visual Studio. NET to add a stored procedure
The following describes how to add a stored procedure to an existing SQL Server database in Visual Studio. NET 2003. You need to use the server resource manager to open a new Stored Procedure Template, edit it, and save it to the database. The following is an example of step-by-step implementation of this process:
Open Visual Studio. NET, open an existing database project (such as the project started earlier in this article), or start a new project.
In Server Explorer (Server resource manager), expand the Data Connections (Data connection) tree, find the database you want to use (DotNetKB), and then in Stored Procedures (Stored procedure) right-click the node to open the context menu.
Select New Stored Procedure from the context menu to open a Stored Procedure template in the Visual Studio. NET editor space. Now you can type the content.
After editing, close the editing page in the editor. Visual Studio. NET saves the content to the database using the name of the stored procedure. If the entered content is incorrect, the editor reports these errors to you. You can correct these errors before saving the stored procedure (see Figure 11 ).
The following is a simple example of a stored procedure. It returns a topic list.
Create procedure TopicsGetList
AS
Set nocount on -- the value of the affected row is not returned.
SELECT
ID,
Title,
Description
FROM
Topics
ORDER
Title
RETURN @ ERROR
In this example, there are several points to note. First, pay attention to the set nocount on row. It tells SQL Server to stop computing the affected number of rows for the query and stop returning the value to the called function. This is an unnecessary extra task. Second, the RETURN @ ERROR line at the end is very important. This line of code returns the integer of an error in SQL Server. You can use this code in the call routine to complete other diagnosis and error handling operations. You do not need to perform any operations now, but they are two good habits to follow when creating a stored procedure.
The following is a more complex stored procedure. This process is used to retrieve a single topic record from the database. You will find some additional items, including the input parameters, output parameters that return specific values, and some program code that checks the input parameters and returns errors when necessary.