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

王朝mssql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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 accumulated 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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航