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
