Đang chuẩn bị liên kết để tải về tài liệu:
Microsoft SQL Server 2008 R2 Unleashed- P97

Microsoft SQL Server 2008 R2 Unleashed- P97:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major releases.Microsoft SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 904 CHAPTER 28 Creating and Managing Stored Procedures Returning Procedure Status Most programming languages are able to pass a status code to the caller of a function or subroutine. A value of 0 generally indicates that the execution was successful. SQL Server stored procedures are no exception. SQL Server automatically generates an integer status value of 0 after successful completion of a stored procedure. If SQL Server detects a system error a status value between -1 and -99 is returned. You can use the RETURN statement to explicitly pass a status value less than -99 or greater than 0. The calling batch or procedure can set up a local variable to retrieve and check the return status. In Listing 28.16 the stored procedure returns the year-to-date sales for a given title as a result set. If the title does not exist to avoid returning an empty result set the procedure returns the status value -101. In the calling batch or stored procedure you need to create a variable to hold the return value. The variable name is passed the EXECUTE keyword and the procedure name as shown in Listing 28.16. LISTING 28.16 Returning a Status Code from a Stored Procedure IF EXISTS SELECT FROM sys.procedures WHERE schema_id schema_id dbo AND name N ytd_sales2 DROP PROCEDURE dbo.ytd_sales2 GO --Create the procedure CREATE PROC ytd_sales2 @title varchar 80 AS IF NOT EXISTS SELECT FROM titles WHERE title @title RETURN -101 SELECT ytd_sales FROM titles WHERE title @title RETURN GO -- Execute the procedure DECLARE @status int EXEC @status ytd_sales2 Life without Fear IF @status -101 PRINT No title with that name found. go ytd_sales 111 Download from www.wowebook.com Debugging Stored Procedures Using SQL Server Management Studio 905 -- Execute the procedure DECLARE @status int EXEC @status ytd_sales2 Life without Beer IF @status -101 PRINT No title with that name found. go No title with that name found. Return values can also be passed back and captured by client applications developed in .