数据库

img hiyaolee

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

发表于2004/10/6 17:25:00  1289人阅读

分类: Oracle & Sql Server

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


 3.分批取數存儲過程

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

 

create  PROCEDURE GetPageData

@tblName   varchar(255),       -- 表名

@strGetFields varchar(1000) = '*',  -- 需要返回的列

@fldName varchar(255)='',      -- 排序的字段名

@PageSize   int = 10,          -- 页尺寸

@PageIndex  int = 1,           -- 页码

@doCount  bit = 0,   -- 返回记录总数, 0 值则返回

@OrderType bit = 0,  -- 设置排序类型, 0 值则降序

@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)       -- 主语句

declare @strTmp   varchar(110)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型

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 

--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount0的情况

else

begin

if @OrderType != 0

begin

    set @strTmp = '<(select min'

set @strOrder = ' order by [' + @fldName +'] desc'

--如果@OrderType不是0,就执行降序,这句很重要!

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

--如果是第一页就执行以上代码,这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['

    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @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 + ' ' + @strOrder

end

end  

exec (@strSQL)

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 

 

阅读全文
0 0

相关文章推荐

img
取 消
img