SQL常见通用存储过程

一.  获取表中条目总数的存储过程

代码：

ALTER PROCEDURE [dbo].[P_GetCounter]

(

@TableName VARCHAR(355), -- 表名

@Condition NVARCHAR(2000) = '' -- 查询条件

)

As

Begin

--直接取MS SQL的总数记录

IF @Condition=''

    SELECT rows FROM sysindexes WHERE id = object_id(@TableName) AND indid IN (0,1)

ELSE

BEGIN

SET @Condition = ' WHERE ' +@Condition

DECLARE @SQL NVARCHAR(4000)

SET @SQL = 'SELECT COUNT(0) FROM '+@TableName + @Condition

EXEC(@SQL)

print(@SQL)

END

End

详解：sysindexes 系统索引对象

结构：

列名	数据类型	描述
id	int	表 ID（如果 indid = 0 或 255）。否则为索引所属表的 ID。
status	int	内部系统状态信息。
first	binary(6)	指向第一页或根页的指针。
indid	smallint	索引 ID： 1 = 聚集索引 >1 = 非聚集 255 = 具有 text 或 image 数据的表条目
root	binary(6)	如果 indid >= 1 和 < 255，root 是指向根页的指针。如果 indid = 0 或 indid = 255，root 是指向最后一页的指针。
minlen	smallint	最小行大小。
keycnt	smallint	键的数目。
groupid	smallint	在其上创建对象的文件组 ID。
dpages	int	如果 indid = 0 或 indid = 1，dpages 是已用数据页的计数。如果 indid = 255，其设置为 0。否则是已用索引页的计数。
reserved	int	如果 indid = 0 或 indid = 1，reserved 是分配给所有索引和表数据的页计数。如果 indid = 255，reserved 是分配给 text 或 image 数据的页计数。否则是分配给索引的页计数。
used	int	如果 indid = 0 或 indid = 1，used 是用于所有索引和表数据的总页数。如果 indid = 255，used 是用于 text 或 image 数据的页计数。否则是用于索引的页计数。
rowcnt	bigint	基于 indid = 0 和 indid = 1 的数据级行计数。如果 indid = 255，rowcnt 设置为 0。
rowmodctr	int	对自上次更新表的统计后插入、删除或更新行的总数进行计数。
xmaxlen	smallint	最大行大小。
maxirow	smallint	最大非叶索引行大小。
OrigFillFactor	tinyint	创建索引时使用的起始填充因子值。不保留该值；然而，如果需要重新创建索引但记不住当初使用的填充因子，则该值可能很有帮助。
reserved1	tinyint	保留。
reserved2	int	保留。
FirstIAM	binary(6)	保留。
impid	smallint	保留。索引实现标志。
lockflags	smallint	用于约束经过考虑的索引锁粒度。例如，对于本质上是只读的查找表，可以将其设置为仅进行表级锁定以使锁定成本减到最小。
pgmodctr	int	保留。
keys	varbinary(816)	组成索引键的列 ID 列表。
name	sysname	表名（如果 indid = 0 或 255）。否则为索引的名称。
statblob	image	统计 BLOB。
maxlen	int	保留。
rows	int	基于 indid = 0 和 indid = 1的数据级行数，该值对于 indid >1 重复。如果 indid = 255，rows 设置为 0。提供该列是为了向后兼容。

 

Indid：1时为聚簇索引；0为表;>1为非聚簇索引；255为图片或者TEXT image等。

count(0),count(*),count(某个字段)区别：

count(*) 会忽略NULL

 

 

二. 分页获取表记录

1．按主键排序

---------按主键排序

---分页

ALTER Procedure [dbo].[P_Pager]

(

@PageSize INT = 20, -- 每页记录数

@CurrentPage INT = 1, -- 当前页码

@TableNames VARCHAR(255), -- 表名

@IndexID VARCHAR(20) = 'ID' ,-- 主键

@Fields VARCHAR(500) = '*', -- 需要返回的列

@Condition NVARCHAR(3000) = '', -- 查询条件

@OrderType BIT = 1 -- 排序类型,1-降序(默认)  0-升序

)

As

DECLARE @SQL NVARCHAR(4000) -- 主语句

 

 

Begin

    if(@Condition != '') Set @Condition = ' Where ' + @Condition

   

    DECLARE @_PAGESIZE VARCHAR(20) --将每页显示数转换为字符串

    SET @_PAGESIZE=CAST(@PageSize AS VARCHAR(20))

 

    DECLARE @_OrderType VARCHAR(5) --排序规则

    IF(@OrderType = 0) SET @_OrderType = ' ASC'

    ELSE  SET @_OrderType = ' DESC'

 

    if(@CurrentPage <= 1)

    BEGIN

        SET @SQL = 'SELECT TOP ' + @_PAGESIZE + ' '+@Fields +' FROM '+@TableNames+' ' + @Condition + ' ORDER BY '+@IndexID+@_OrderType

    END

    ELSE

    BEGIN

        DECLARE @MARK VARCHAR(1)

        DECLARE @SubSelect NVARCHAR(4000) --  子查询

        DECLARE @LASTID INT --获取前ID

        SET @LASTID=CAST((@CurrentPage-1)*@PageSize AS VARCHAR(20))

 

        IF(@OrderType = 0) --升序

       BEGIN

       SET @SubSelect = 'SELECT @A=CONVERT(VARCHAR(50),MAX('+@IndexID+')) FROM (SELECT TOP '+str(@LASTID)+' '+ @IndexID + ' FROM ' + @TableNames + @Condition + ' ORDER BY '+ @IndexID+@_OrderType + ') AS TT1';

       SET @MARK = '>';

       END

        ELSE  --降序

        Begin

            SET @SubSelect = 'SELECT @A=CONVERT(VARCHAR(50),MIN('+@IndexID+')) FROM (SELECT TOP '+str(@LASTID)+' '+ @IndexID + ' FROM ' + @TableNames + @Condition + ' ORDER BY '+ @IndexID+@_OrderType + ') AS TT1';

            SET @MARK = '<';

        END

        DECLARE @SID VARCHAR(50)

        --PRINT(@SubSelect)

        EXEC sp_executesql @SubSelect,N'@A VARCHAR(50) output',@SID OUTPUT

        --如果有条件,将WHERE替换为AND

        IF (@Condition != '') SET @Condition = ' AND ' + RIGHT(@Condition,LEN(@Condition)-6)

       

        Set @SQL = 'SELECT TOP ' + CAST(@_PAGESIZE AS VARCHAR(30)) + ' ' + @Fields + ' FROM ' + @TableNames + ' WHERE ' + @IndexID + @MARK + ''''+ @SID + '''' + @Condition + ' ORDER BY '+@IndexID+@_OrderType

    End

    PRINT(@SQL)

    Exec (@SQL)

End

 

2．按非主键排序

 

---------按非主键排序

---分页

ALTER Procedure [dbo].[P_PagerNotID]

(

@PageSize int = 20, -- 每页记录数

@CurrentPage int = 1, -- 当前页码

@TableName varchar(355), -- 表名

@Fields varchar(1000) = '*', -- 需要返回的列,可以为*或表名.*或是字段名称列表，如果列表中带有*,则会把XID也读取出来

@Condition varchar(1500) = '', -- 查询条件

@OrderNames varchar(200) = '' -- 排序字段，包括排序类型,必须设置排序字段,包含ASC 或DESC

 

)

As

Declare @SQL nvarchar(4000)

 

Begin

if(@Condition != '')

Set @Condition = ' WHERE ' + @Condition

 

Set @SQL = ''

 

 

if(@CurrentPage <= 1)

Set @SQL =  'SELECT  TOP ' + cast(@PageSize as varchar(20)) + ' ' + @Fields + ',1 as RowNumber FROM ' + @TableName + @Condition + ' ORDER BY ' + @OrderNames

else

Begin

DECLARE @TOTAL VARCHAR(50)

SET @TOTAL = cast(( @CurrentPage)*@PageSize as  varchar(20))

 

SET @SQL = @SQL + 'SELECT  TOP ' + cast(@PageSize as varchar(20)) + ' * FROM (SELECT  TOP ' + @TOTAL + ' ' + @Fields + ',ROW_NUMBER() OVER (ORDER BY ' + @OrderNames +') AS RowNumber From ' + @TableName + @Condition + ')T WHERE RowNumber BETWEEN '+cast(( @CurrentPage-1)*@PageSize+1 as  varchar(20))+' AND '+@TOTAL--+' ORDER BY ' + @OrderNames

End

End

print (@SQL)

Exec (@SQL)

 

注意的时ROW_NUMBER() OVER (ORDER BY FIELDS)//获取行索引d