Đang chuẩn bị liên kết để tải về tài liệu:
Executing SQL Server Stored Procedures phần 2

The output from this program is as follows: New ProductID = 81 Of course, depending on the existing rows in your Products table | The output from this program is as follows New ProductID 81 Of course depending on the existing rows in your Products table you ll get a different result. Executing the AddProduct2 Stored Procedure As you ll see the AddProduct2 procedure is similar to AddProduct except that it uses a RETURN statement instead of an OUTPUT parameter to return the ProductID for the new row. Listing 8.13 shows the AddProduct2.sql script that creates the AddProduct2 procedure. You ll need to run this script before running the C program. Listing_8.13 _ADDPRODUCT2.SQL AddProduct2.sql creates a procedure that adds a row to the Products table using values passed as parameters to the procedure. The procedure returns the ProductID of the new row using a RETURN statement CREATE PROCEDURE AddProduct2 @MyProductName nvarchar 40 @MySupplierID int @MyCategoryID int @MyQuantityPerUnit nvarchar 20 @MyUnitPrice money @MyUnitsInStock smallint @MyUnitsOnOrder smallint @MyReorderLevel smallint @MyDiscontinued bit AS - declare the @MyProductID variable DECLARE @MyProductID int - insert a row into the Products table INSERT INTO Products ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued VALUES @MyProductName @MySupplierID @MyCategoryID @MyQuantityPerUnit @MyUnitPrice @MyUnitsInStock @MyUnitsOnOrder @MyReorderLevel @MyDiscontinued - use the SCOPE_IDENTITY function to get the last - identity value inserted into a table performed within - the current database session and stored procedure - so SCOPE_IDENTITY returns the ProductID for the new row - in the Products table in this case SET @MyProductID SCOPE_IDENTITY RETURN@MyProductID Notice the RETURN statement at the end to return @MyProductID. Because AddProduct2 doesn t return a result set of rows you use the same four steps shown in the previous section to execute the procedure using ADO.NET. The only difference is in the construction of your EXECUTE .