Create Stored Procedures

2,7 Tạo thủ tục lưu trữ Thủ tục lưu trữ cho phép bạn thực hiện số lượng lớn trên các hồ sơ, chẳng hạn như cập nhật, chèn, và xóa dữ liệu khi cần thiết
Nội dung trích xuất từ tài liệu:
Create Stored Procedures 2.7 Create Stored ProceduresStored procedures allow you to perform bulk operations on records, such as updating,inserting, and deleting data as needed. This tutorial shows you how to create storedprocedures as well as how to test them from within the Server Explorer.Viewing data is great, but you need to perform bulk operations such as adding, updating,and deleting records. You know that you will be using a stored procedure to performthese tasks, and you will be using them more than once in your application. How do youcreate stored procedures from VS?TechniqueStored procedures are the way to go when you want add, delete, or update records orwhen you want to use a parameter. When you have a task that requires multiple actions,including temporary tables, stored procedures give you this ability. Stored procedures arepowerful to use and easy to create.Note To create stored procedures, you will use what is called Transact-SQL (T-SQL). Although this chapter will present some simple commands to show the interface used to create stored procedures from VS .NET, Chapter 6, Creating Transact-SQL Commands, goes into more detail on the commands of the language.As with views, you will use a designer within Visual Studio. Unlike the Views designer,the stored procedure designer is not visual initially, but more text oriented. However, youcan pull up a visual designer after you are in the text designer.When youre creating a new stored procedure, you will right-click on the StoredProcedures node in the database to which you want to add the stored procedure, and thenyou will choose New Stored Procedure. To edit existing stored procedures, you willhighlight the stored procedure, right-click, and choose Edit Stored Procedure.After the stored procedure is open, you will see a select statement or a number of T-SQLstatements. If it is a new stored procedure, you can right-click and choose Insert SQL.You will be taken to the Query Builder, which happens to look like the View designer. Ifit is an existing stored procedure, you can place the cursor within a block of SQL code,which is outlined with a blue line, and choose Design SQL Block from the right-clickmenu, as shown in Figure 2.12. Figure 2.12. You can also set break points in your stored procedures using this menu.You will then see the SQL block displayed once again in the Query Builder.When specifying parameters that can be used as criteria in stored procedures, you willuse the @ symbol in front of the parameter name, and declare them at the top of yourstored procedure. Again, you can see this in Figure 2.12.StepsFor this How-To, you are going to create a simple Select statement with a parameter,listing customers for a given city. If youre not already there, open the Server Explorerand expand the Northwind database. 1. Right-click on the Stored Procedures node, and then choose New Stored Procedure. You will be taken into a new page that is a template for stored procedure text. You will see the following listed: 2. CREATE PROCEDURE dbo.StoredProcedure1 3. /* 4. ( 5. @parameter1 datatype = default value, 6. @parameter2 datatype OUTPUT 7. ) 8. */ 9. AS 10. /* SET NOCOUNT ON */ 11. RETURN 12. Replace all the text displayed with the following: 13. CREATE PROCEDURE dbo.spListCustomersForCountry 14. @parCountry char 15. AS 16. Select * From Customers where Country = @parCountry 17. RETURN With this, you can see the use of the parameter. 18. Save the stored procedure.How It WorksTo test the stored procedure that you just created, you can right-click on the block of codeand choose Design SQL Block from the menu. You can then click on the Run Querytoolbar button and fill in the parCity parameter with USA when the dialog box ispresented. You will then see the information displayed in the Results pane.You will see examples of using stored procedures in the next chapter, which discussesusing ADO.NET with SQL Server objects.CommentsAlthough you can create stored procedures on-the-fly and not save them in the database,it is sometimes necessary and desirable to save them permanently so that you can use thesame code in different places in your application.