SqlServer分批取數與相關ID的數據表

1.分批取數
declare @P1 int
set @P1=180150000
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=3
exec sp_cursoropen @P1 output,
N@#select top 3 * from authors@#,
@P2 output,
@P3 output,
@P4 output
select @P1 , @P2 , @P3 , @P4
go
exec sp_cursorfetch 180150000, 16, 1, 1
go
exec sp_cursorfetch 180150000, 16, 2, 1
go
exec sp_cursorfetch 180150000, 16, 3, 1
go
exec sp_cursorfetch 180150000, 16, 4, 1
go
exec sp_cursorclose 180150000
go


exec sp_cursorfetch 180150000, 16, 1, 10 --從第1筆起,取10筆
exec sp_cursorclose 180150000
go  

2.取相關ID的數據表

The table-valued function fn_FindReports(InEmpID), which -- given an Employee ID -- returns a table corresponding to all the employees that report to the given employee directly or indirectly. This logic is not expressible in a single query and is a good candidate for implementing as a user-defined function.

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold aclearcase/" target="_blank" >ccumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key,
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees
   WHERE empid = @InEmpId
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
   
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title
   FROM @reports
   RETURN
END
GO


 

原文转自：http://www.ltesting.net