Data tables for SQL Server batches and related IDs

Server|sqlserver
1. Batch Fetching
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--from 1th pen, take 10 pen
EXEC sp_cursorclose 180150000
Go

2. The data table of the relative ID

The table-valued function Fn_findreports (inempid), which--given a Employee ID--Returns a table corresponding to all t He employees the given employee directly or indirectly. This logic isn't expressible in a single query and are 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 (m) not NULL,
Mgrid nchar (5),
Title nvarchar (30))
/*returns a result set this lists all the employees who-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 (m) 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 the iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
--Insert employees who 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
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