Executing SQL Server Stored Procedures phần 1

Executing SQL Server Stored Procedures In Chapter 4, you saw how to create and execute SQL Server stored procedures using TSQL. You execute a stored procedure using the T-SQL EXECUTE statement.
Nội dung trích xuất từ tài liệu:
Executing SQL Server Stored Procedures phần 1Executing SQL Server Stored ProceduresIn Chapter 4, you saw how to create and execute SQL Server stored procedures using T-SQL. You execute a stored procedure using the T-SQL EXECUTE statement. In thissection, youll see how to execute SQL Server procedures using ADO.NET.In Table 8.1, shown earlier in this chapter, I mentioned the CommandType ofStoredProcedure. Although you can use this CommandType to indicate that a commandis to execute a stored procedure, youre actually better off using the T-SQL EXECUTEcommand to execute a stored procedure. This is because you can read values that arereturned from a stored procedure through a RETURN statement, which you cant dowhen setting the CommandType to StoredProcedure. Also, its a lot easier to understandyour code when you use the EXECUTE command.There are a couple of ways you can execute a stored procedure depending on whetheryour procedure returns a result set (a result set is one or more rows retrieved from a tableby a SELECT statement). Youll learn these two ways to execute a stored procedure next.Executing a Stored Procedure That Does Not Return a Result SetIf your procedure does not return a result set, then you use the following steps to executeit: 1. Create a Command object and set its CommandText property to an EXECUTE statement containing your procedure call. 2. Add any required parameters for the procedure call to your Command object, remembering to set the Direction property for any output parameters to ParameterDirection.Output. These output parameters can be defined using the T- SQL OUTPUT keyword in your procedure call, or returned using a RETURN statement in your actual procedure. 3. Execute your Command object using the ExecuteNonQuery() method. You use this method because the procedure doesnt return a result set. 4. Read the values of any output parameters.Youll see how to use these four steps to call the following two SQL Server storedprocedures: • The first procedure, AddProduct(), will return an output parameter defined using the OUTPUT keyword. • The second procedure, AddProduct2(), will return an output parameter using the RETURN statement.These examples will show you the possible ways to execute a stored procedure usingADO.NET and read the output parameters.Executing the AddProduct() Stored ProcedureIn Chapter 4, you saw how to create a stored procedure in the SQL Server Northwinddatabase. The procedure you saw was named AddProduct(), and Listing 8.11 shows theAddProduct.sql script that creates the AddProduct() procedure. You saw how to run thisscript in Chapter 4. If you didnt already run this script when reading Chapter 4, and youwant to run the example C# program shown later, youll need to run this script.AddProduct() adds a row to the Products table and returns the ProductID of the new rowas an OUTPUT parameter.Listing 8.11: ADDPRODUCT.SQL/* AddProduct.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 in an OUTPUT parameter named @MyProductID*/CREATE PROCEDURE AddProduct @MyProductID int OUTPUT, @MyProductName nvarchar(40), @MySupplierID int, @MyCategoryID int, @MyQuantityPerUnit nvarchar(20), @MyUnitPrice money, @MyUnitsInStock smallint, @MyUnitsOnOrder smallint, @MyReorderLevel smallint, @MyDiscontinued bitAS - 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 SELECT @MyProductID = SCOPE_IDENTITY()Notice the OUTPUT parameter named @MyProductID returned by AddProduct().Because AddProduct() doesnt return a result set, you use the first set of steps outlinedearlier. Lets examine the details of these four steps to execute this stored procedure.Step 1: Create a Command Object and set its CommandText Property to anEXECUTE StatementYour first step is to create a Command object and set its CommandText property to anEXECUTE statement containing the call to AddProduct(); notice the parameterplaceholders used to mark the position where the parameter values will be substituted instep 2:SqlCommand mySqlCommand = mySqlConnection.CreateCommand();mySqlCommand.CommandText = EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, + @MyS ...