1. Batch number declare @ p1 int set @ p1 = 180150000 declare @ p2 int set @ p2 = 8 declare @ p3 int set @ p3 = 1 declare @ p4 intset @ P4 = 3 exec sp_cursoropen @ p1 output, n'select top 3 * from authors', @ P2 output, @ P3 output, @ P4 outputselect @ P1, @ P2, @ P3, @ P4 goexec sp_cursorfetch 180150000, 16, 1, 1goexec sp_cursorfetch 180150000, 16, 2, 1goexec sp_cursorfetch 180150000, 16, 3, 1GOEXEC SP_CURSORFETCH 180150000, 16, 4, 1GOEXEC sp_cursorclose 180150000GOEXEC sp_cursorfetch 180150000, 16, 1, 10 - from the first one, take 10 EXEC SP_CURSORCLOSE 180150000GO
2. Take the relevant ID data sheet
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 * / ASBEGIN 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 E mpid = @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 returnendgo3. Divided number of stored procedures
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
Create Procedure GetPageData
@tblname varchar (255), - Table name
@StrGetfields varchar (1000) = '*', - the column that needs to be returned
@fldname varchar (255) = '', - Sort field name
@PageSize Int = 10, - Page Size
@PageIndex INT = 1, - Page
@docount bit = 0, - Return to the total number of records, non-0 values return
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (1500) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (5000) - main statement
Declare @strtmp varchar (110) - Temporary variable
Declare @strorder varchar (400) - Sort Type
IF @docount! = 0
Begin
IF @strwhere! = ''
Set @strsql = 'select count (*) as total from [' @TBLNAME '] Where' @ Strwhere
Else
Set @strsql = 'select count (*) as total from [' @TBLNAME ']'
end
- The above code means that if @Docount passes is not 0, the total number of statistics is performed. All of the following code is @docount is 0
Else
Begin
IF @ORDERTYPE! = 0
Begin
Set @strtmp = '<(SELECT MIN'
Set @strorder = 'Order by [' @fldname "] desc '
- If @ORDERTYPE is not 0, it will be designed, this sentence is very important!
end
Else
Begin
Set @strtmp = '> (SELECT MAX'
Set @strorder = 'Order by [' @fldname '] ASC'end
IF @PageIndex = 1
Begin
IF @strwhere! = ''
Set @STRSQL = 'SELECT TOP' STR (@Pagesize) '' @ strGetfields 'from [' @TBLNAME '] Where' @strwhere ' @strorder
Else
Set @STRSQL = 'SELECT TOP' STR (@Pagesize) '' @ strGetfields 'from [' @TBLNAME ']' @strorder
- If you are the first page, you will implement the above code, which will speed up the execution speed.
end
Else
Begin
- The following code gives @strsql to real execute SQL code
Set @strsql = 'SELECT TOP' STR (@PageSize) '' @ strGetfields 'from ['
@TBLNAME '] Where [' @fldname ']' @strtmp '([' @fldname ']) from (SELECT TOP' STR ((@ PageIndex-1) * @ Pagesize) '[' @fldname ']' @strorder ') as tbltmp)' @strorder
IF @strwhere! = ''
Set @strsql = 'SELECT TOP' STR (@PageSize) '' @ strGetfields 'from ['
@TBLNAME '] Where [' @fldname ']' @strtmp '(['
@fldname ']) from (SELECT TOP' STR ((@ PageIndex-1) * @ PageSize) '['
@fldname '] from [' @TBLNAME '] Where' @Strwhere ''
@strorder ') as tbltmp) and' @Strwhere '' @strorderend
end
EXEC (@strsql)
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
