Microsoft SQL Server 2005 Developer’s Guide- P4

Microsoft SQL Server 2005 Developer’s Guide- P4:This book is the successor to the SQL Server 2000 Developer’s Guide, whichwas extremely successful thanks to all of the supportive SQL Server developerswho bought that edition of the book. Our first thanks go to all of the peoplewho encouraged us to write another book about Microsoft’s incredible new relationaldatabase server: SQL Server 2005.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2005 Developer’s Guide- P4 C h a p t e r 2 : D e v e l o p i n g w i t h T- S Q L 59 The code block that you can see at the top of this listing essentially creates a new tablenamed dbo.FirstHalfDept. This table is based on the rows in the HumanResources.Department table where the DepartmentID is less than or equal to 8. The next codeblock creates a second new table named dbo.SecondHalfDept using the rows in theHumanResources.Department table where the DepartmentID is greater than 8. TheUNION statement will then take these two results sets and join them back together intoa single result set. The results of the union of the dbo.FirstHalfDept table and the dbo.SecondHalfDepttable are shown in the following listing. As you can see, the UNION operationmerged the two tables together back into a single table with the same contents as theoriginal HumanResources.Department table that was used as a basis to create theother two tables:(8 row(s) affected)(8 row(s) affected)DepartmentID Name GroupName------------ ---------------------------------------------------------------1 Engineering Research and Development2 Tool Design Research and Development3 Sales Sales and Marketing4 Marketing Sales and Marketing5 Purchasing Inventory Management6 Research and Development Research and Development7 Production Manufacturing8 Production Control Manufacturing9 Human Resources Executive General and Administration10 Finance Executive General and Administration11 Information Services Executive General and Administration12 Document Control Quality Assurance13 Quality Assurance Quality Assurance14 Facilities and Maintenance Executive General and Administration15 Shipping and Receiving Inventory Management16 Executive Executive General and Administration(16 row(s) affected)Using SubqueriesA subquery is a query that’s nested inside of another T-SQL query. Subqueries can benested within SELECT, INSERT, UPDATE, or DELETE statements. More informationabout using INSERT, UPDATE, and DELETE statements is presented later in this chapter.60 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e The following example illustrates using a subquery to retrieve all of the names of the employees in the AdventureWorks database who have the title of Tool Designer: SELECT FirstName, LastName, e.Title FROM Person.Contact c Join HumanResources.Employee e On e.ContactID = c.ContactID WHERE EmployeeID IN (SELECT EmployeeID FROM HumanResources.Employee WHERE Title = Tool Designer) The SELECT statement specifies that the result set will contain three columns. The FirstName and LastName columns come from the Person.Contact table, while the Title column comes from the HumanResources.EmployeeID table. The Person. Contact table and the HumanResources.EmployeeID table are joined on the ContactID column. The subquery then further restricts the result set by specifying that only the rows from the HumanResources.EmployeeID table will be used where the value in the Title column is equal to ‘Tool Designer’. FirstName LastName Title ---------- --------- -------------- Thierry D’Hers Tool Designer Janice Galvin Tool Designer (2 row(s) affected) NOTE In many cases, the same results that are produced using subqueries can also be produced using joins. Row-at-a-Time Processing Using Cursors T-SQL is a set-at-a-time language that is designed for dealing with sets of data at one time. However, there are circumstances where you may need to deal with the data contained in a table or result set on a row-by-row basis. Cursors are the T-SQL mechanism that enable single-row processing. Cursors limit scalability because they hold locks on the table while the cursor is open; however, they do provide a great deal of flexibility in dealing with individual results in a result set. The following example illustrates using a cursor to process a result set based on the HumanResources.Department table one row at a time. C h a p t e r 2 : D e v e l o p i n g w i t h T- S Q L 61DECLARE @ThisDept INTDECLARE DeptCursor CURSOR FOR SELECT DepartmentID from HumanResources.DepartmentOPEN DeptCursorWHILE @@FETCH_STATUS = 0BEGIN PRINT Processing Department: + RTRIM(CAST(@ThisDept AS VARCHAR(10))) FETCH NEXT FROM DeptCursor INTO @ThisDeptENDCLOSE DeptCursorDEALLOCATE DeptCursor At the top of this listing you can see where two variables are declared. The firstvariable, named @ThisDept, will be used to store the value of the DepartmentIDcolumn that’s returned by the cursor. The next variable is a handle for the cursornamed DeptCursor. The rows this cursor will operate over are defined in the followingSELECT statement, which returns just the DepartmentID column for all of the rowsin the HumanResources.Department table. After the cursor has been defined, it’sthen opened using the OPEN statement, and then a WHILE loop is used to processall of the rows returned from the HumanResources.Department table. The WHILEloop will continue processing until the value of the @@FETCH_STATUS variableis not equal to z ...