www.9778.com 8

MSSQL SERVER中易混淆的数据类型

核心提示:事例数据库表:企业信息表表名称prd_wxt_enterprise_tbl_EnterpriseInfo数据来源
存储数据企业信息表主键/外键 字段名称说明类型

本文转自:

背景:

核心提示:数据类弄是数据的一种属性,表示数据所表示信息的类型

1)事例数据库表: 企业信息表 表名称
prd_wxt_enterprise_tbl_EnterpriseInfo 数据来源 存储数据 企业信息表
主键/外键 字段名称 说明 类型 备注 cEnterpriseId n企业ID Char(10)
sEnterpriseName n企业名称 Varchar(200) sShortName n简称 Varchar(50)
sAddress n地址 nVarchar(200) E_system n企业所属系统 Char(1)

版权声明:作者:jiankunking 出处: 
本文版权归作者和CSDN共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。

遇到这样一种情况:动态向存储过程中传入表名和表的某些属性(这里用到的是主键ID),然后利用这两个变量查出一条数据的某些字段值,然后再利用这些值进行逻辑运算(自己的逻辑),最后将结果输出。话不多说,直接上图,看是否是你想要的结果:

数据类弄是数据的一种属性,表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然,不同的程序语言都具有不同的特点,所定义的数据类型的各类和名称都或多或少有些不同。SQLServer
提供了 25 种数据类型: ・Binary [(n)] ・Varbinary [(n)] ・Char
[(n)] ・Varchar[(n)] ・Nchar[(n)] ・Nvarchar[(n)] ・Datetime
・Smalldatetime ・Decimal[(p[,s])] ・Numeric[(p[,s])]
・Float[(n)] ・Real ・Int ・Smallint ・Tinyint ・Money ・Smallmoney
・Bit ・Cursor ・Sysname ・Timestamp ・Uniqueidentifier ・Text ・Image
・Ntext 1.二进制数据类型

产品表 表名称 Shop_tbl_ProductInfo 数据来源 存储数据 产品表 主键/外键
字段名称 说明 类型 备注 P_Code(PK)
n产品SKU号,即下单号,产品系统编号(p123456789) Char(10) not null
EnterpriseId n企业ID Char(10) not null P_Name n产品名称 Nvarchar(100)
not null P_Price n产品价格 Money P_UserPrice n产品会员价 Money Remark
n备注 Nvarchar(500) 2)要实现的功能:
删除旧企业数据及相关的产品,要求用嵌套存储过程实现
3)存储过程:包括两个存储过程
a)存储过程一:根据企业ID,删除该企业及下面的产品: 存储过程代码如下:
/* 功能:删除企业,同时删除关联的表,包括企业表及其关联的产品表数据
创建人: 创建日期:2007-4-10 修改日期:2007-4-10 */ CREATE PROCEDURE
[DeleteEnterprise] ( @EnterpriseID nvarchar(10) ) AS declare @strSQL
nvarchar(4000) –删除企业产品表 select @strSQL = delete from
Shop_tbl_ProductInfo where EnterpriseId=+@EnterpriseID+; exec(@strSQL)
–删除企业表 select @strSQL = delete from
prd_wxt_enterprise_tbl_EnterpriseInfo where
cEnterpriseID=+@EnterpriseID+; exec(@strSQL) GO

分页存储过程一:

www.9778.com 1 

二进制数据包括 Binary、Varbinary 和 Image Binary
数据类型既可以是固定长度的(Binary),也可以是变长度的。 Binary[(n)] 是 n
位固定的二进制数据。其中,n 的取值范围是从 1 到 8000。其存储窨的大小是 n

b)存储过程二:选出旧企业的数据,然后通过循环嵌套的方式,通过循环语句调用存储过程一,删除所有的旧企业数据及其产品数据
存储过程代码如下: /* 功能:删除旧企业数据,同时删除关联的表,包括
创建人: 创建日期:2007-4-11 修改日期:2007-4-11 */ CREATE PROCEDURE
[DeleteOldEnterprise] AS declare @strSQL nvarchar(4000) declare
@EnterpriseID nvarchar(10) DECLARE Enterprise_CURSOR Cursor FOR Select
cEnterpriseId from prd_wxt_enterprise_tbl_EnterpriseInfo where
e_system=1 or e_system is null OPEN Enterprise_CURSOR Fetch next from
Enterprise_CURSOR into @EnterpriseID –以下一直到END都是循环语句 WHILE
@@FETCH_STATUS = 0 BEGIN –下面这行是调用存储过程一删除企业及其产品数据
exec DeleteEnterprise @EnterpriseID Fetch next from Enterprise_CURSOR
into @EnterpriseID END CLOSE Enterprise_CURSOR DEALLOCATE
Enterprise_CURSOR GO

 

www.9778.com 2

  • 4 个字节。 Varbinary[(n)] 是 n 位变长度的二进制数据。其中,n
    的取值范围是从 1 到 8000。其存储窨的大小是 n + 4个字节,不是n 个字节。
    在 Image 数据类型中存储的数据是以位字符串存储的,不是由 SQL Server
    解释的,必须由应用程序来解释。例如,应用程序可以使用BMP、TIEF、GIF 和
    JPEG 格式把数据存储在 Image 数据类型中。 (2)字符数据类型

[sql] view
plain
copy
print?

说明:【区域1为要用来测试的表】【  区域2 为表中数据】【区域3
为表中数据jan+feb+mar列值的和5】

字符数据的类型包括 Char,Varchar 和 Text
字符数据是由任何字母、符号和数字任意组合而成的数据。 Varchar
是变长字符数据,其长度不超过 8KB。Char 是定长字符数据,其长度最多为
8KB。超过 8KB 的ASCII 数据可以使用Text数据类型存储。例如,因为 Html
文档全部都是 ASCII 字符,并且在一般情况下长度超过 8KB,所以这些文档可以
Text 数据类型存储在SQL Server 中。 (3)Unicode 数据类型

  1. –/*—–存储过程 分页处理 孙伟 2005-03-28创建 ——-*/   
  2. –/*—– 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 ——-*/   
  3. –/*—–存储过程 分页处理 孙伟 2005-04-21修改 添加Distinct查询功能——-*/   
  4. –/*—–存储过程 分页处理 孙伟 2005-05-18修改 多字段排序规则问题——-*/   
  5. –/*—–存储过程 分页处理 孙伟 2005-06-15修改 多字段排序修改——-*/   
  6. –/*—–存储过程 分页处理 孙伟 2005-12-13修改 修改数据分页方式为top max模式性能有极大提高——-*/   
  7. –/*—–缺点:相对之前的not in版本主键只能是整型字段,如主键为GUID类型请使用not in 模式的版本——-*/   
  8. CREATE PROCEDURE dbo.proc_ListPageInt   
  9. (   
  10. @tblName nvarchar(200), —-要显示的表或多个表的连接   
  11. @fldName nvarchar(500) = ‘*’, —-要显示的字段列表   
  12. @pageSize int = 10, —-每页显示的记录个数   
  13. @page int = 1, —-要显示那一页的记录   
  14. @pageCount int = 1 output, —-查询结果分页后的总页数   
  15. @Counts int = 1 output, —-查询到的记录数   
  16. @fldSort nvarchar(200) = null, —-排序字段列表或条件   
  17. @Sort bit = 0, —-排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)–程序传参如:’ SortA Asc,SortB Desc,SortC ‘)   
  18. @strCondition nvarchar(1000) = null, —-查询条件,不需where   
  19. MSSQL SERVER中易混淆的数据类型。@ID nvarchar(150), —-主表的主键   
  20. @Dist bit = 0 —-是否添加查询字段的 DISTINCT 默认0不添加/1添加   
  21. )   
  22. AS   
  23. SET NOCOUNT ON   
  24. Declare @sqlTmp nvarchar(1000) —-存放动态生成的SQL语句   
  25. Declare @strTmp nvarchar(1000) —-存放取得查询结果总数的查询语句   
  26. Declare @strID nvarchar(1000) —-存放取得查询开头或结尾ID的查询语句   
  27.   
  28. Declare @strSortType nvarchar(10) —-数据排序规则A   
  29. Declare @strFSortType nvarchar(10) —-数据排序规则B   
  30.   
  31. Declare @SqlSelect nvarchar(50) —-对含有DISTINCT的查询进行SQL构造   
  32. Declare @SqlCounts nvarchar(50) —-对含有DISTINCT的总数查询进行SQL构造   
  33.   
  34.   
  35. if @Dist = 0   
  36. begin   
  37. set @SqlSelect = ‘select ‘   
  38. set @SqlCounts = ‘Count(*)’   
  39. end   
  40. else   
  41. begin   
  42. set @SqlSelect = ‘select distinct ‘   
  43. set @SqlCounts = ‘Count(DISTINCT ‘+@ID+’)’   
  44. end   
  45.   
  46.   
  47. if @Sort=0   
  48. begin   
  49. set @strFSortType=’ ASC ‘   
  50. set @strSortType=’ DESC ‘   
  51. end   
  52. else   
  53. begin   
  54. set @strFSortType=’ DESC ‘   
  55. set @strSortType=’ ASC ‘   
  56. end   
  57.   
  58.   
  59.   
  60. ——–生成查询语句——–   
  61. –此处@strTmp为取得查询结果数量的语句   
  62. if @strCondition is null or @strCondition=” –没有设置显示条件   
  63. begin   
  64. set @sqlTmp = @fldName + ‘ From ‘ + @tblName   
  65. set @strTmp = @SqlSelect+’ @Counts=’+@SqlCounts+’ FROM ‘+@tblName  
  66. set @strID = ‘ From ‘ + @tblName   
  67. end   
  68. else   
  69. begin   
  70. set @sqlTmp = + @fldName + ‘From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition   
  71. set @strTmp = @SqlSelect+’ @Counts=’+@SqlCounts+’ FROM ‘+@tblName + ‘ where (1>0) ‘ + @strCondition   
  72. set @strID = ‘ From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition   
  73. end   
  74.   
  75. —-取得查询结果总数量—–   
  76. exec sp_executesql @strTmp,N’@Counts int out ‘,@Counts out   
  77. declare @tmpCounts int   
  78. if @Counts = 0   
  79. set @tmpCounts = 1   
  80. else   
  81. set @tmpCounts = @Counts   
  82.   
  83. –取得分页总数   
  84. set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize   
  85.   
  86. /**//**当前页大于总页数 取最后一页**/   
  87. if @page>@pageCount   
  88. set @page=@pageCount   
  89.   
  90. –/*—–数据分页2分处理——-*/   
  91. declare @pageIndex int –总数/页大小   
  92. declare @lastcount int –总数%页大小   
  93.   
  94. set @pageIndex = @tmpCounts/@pageSize   
  95. set @lastcount = @tmpCounts%@pageSize   
  96. if @lastcount > 0   
  97. set @pageIndex = @pageIndex + 1   
  98. else   
  99. set @lastcount = @pagesize   
  100.   
  101. –//***显示分页   
  102. if @strCondition is null or @strCondition=” –没有设置显示条件   
  103. begin   
  104. if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 –前半部分数据处理   
  105. begin   
  106. if @page=1   
  107. set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  108. +’ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  109. else   
  110. begin   
  111. set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  112. +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName  
  113. +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’   
  114. +’ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  115. end   
  116. end   
  117. else   
  118. begin   
  119. set @page = @pageIndex-@page+1 –后半部分数据处理   
  120. if @page <= 1 –最后一页数据显示   
  121. set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  122. +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  123. else   
  124. set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  125. +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName  
  126. +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’   
  127. +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  128. end   
  129. end   
  130.   
  131. else –有查询条件   
  132. begin   
  133. if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 –前半部分数据处理   
  134. begin   
  135. if @page=1   
  136. set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  137. +’ where 1=1 ‘ + @strCondition + ‘ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  138. else   
  139. begin   
  140. set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  141. +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName  
  142. +’ where (1=1) ‘ + @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’   
  143. +’ ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  144. end   
  145. end   
  146. else   
  147. begin   
  148. set @page = @pageIndex-@page+1 –后半部分数据处理   
  149. if @page <= 1 –最后一页数据显示   
  150. set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  151. +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  152. else   
  153. set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName  
  154. +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName  
  155. +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’   
  156. +’ ‘+ @strCondition+’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType   
  157. end   
  158. end   
  159.   
  160. ——返回查询结果—–   
  161. exec sp_executesql @strTmp   
  162. –print @strTmp   
  163. SET NOCOUNT OFF   
  164. GO   

    –/—–存储过程 分页处理 孙伟 2005-03-28创建 ——-/
    –/—– 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 ——-/
    –/—–存储过程 分页处理 孙伟 2005-04-21修改 添加Distinct查询功能——-/
    –/—–存储过程 分页处理 孙伟 2005-05-18修改 多字段排序规则问题——-/
    –/—–存储过程 分页处理 孙伟 2005-06-15修改 多字段排序修改——-/
    –/—–存储过程 分页处理 孙伟 2005-12-13修改 修改数据分页方式为top max模式性能有极大提高——-/
    –/—–缺点:相对之前的not in版本主键只能是整型字段,如主键为GUID类型请使用not in 模式的版本——-/
    CREATE PROCEDURE dbo.proc_ListPageInt
    (
    @tblName nvarchar(200), —-要显示的表或多个表的连接
    @fldName nvarchar(500) = ‘*’, —-要显示的字段列表
    @pageSize int = 10, —-每页显示的记录个数
    @page int = 1, —-要显示那一页的记录
    @pageCount int = 1 output, —-查询结果分页后的总页数
    @Counts int = 1 output, —-查询到的记录数
    @fldSort nvarchar(200) = null, —-排序字段列表或条件
    @Sort bit = 0, —-排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)–程序传参如:’ SortA Asc,SortB Desc,SortC ‘)
    @strCondition nvarchar(1000) = null, —-查询条件,不需where
    @ID nvarchar(150), —-主表的主键
    @Dist bit = 0 —-是否添加查询字段的 DISTINCT 默认0不添加/1添加
    )
    AS
    SET NOCOUNT ON
    Declare @sqlTmp nvarchar(1000) —-存放动态生成的SQL语句
    Declare @strTmp nvarchar(1000) —-存放取得查询结果总数的查询语句
    Declare @strID nvarchar(1000) —-存放取得查询开头或结尾ID的查询语句

    Declare @strSortType nvarchar(10) —-数据排序规则A
    Declare @strFSortType nvarchar(10) —-数据排序规则B

    Declare @SqlSelect nvarchar(50) —-对含有DISTINCT的查询进行SQL构造
    Declare @SqlCounts nvarchar(50) —-对含有DISTINCT的总数查询进行SQL构造

存储过程代码如下:

Unicode 数据类型包括 Nchar,Nvarchar 和Ntext 在 Microsoft SQL Server
中,传统的非 Unicode 数据类型允许使用由特定字符集定义的字符。在 SQL
Server安装过程中,允许选择一种字符集。使用 Unicode
数据类型,列中可以存储任何由Unicode 标准定义的字符。在 Unicode
标准中,包括了以各种字符集定义的全部字符。使用Unicode数据类型,所战胜的窨是使用非
Unicode 数据类型所占用的窨大小的两倍。 在 SQL Server 中,Unicode 数据以
Nchar、Nvarchar 和 Ntext
数据类型存储。使用这种字符类型存储的列可以存储多个字符集中的字符。当列的长度变化时,应该使用Nvarchar
字符类型,这时最多可以存储 4000 个字符。当列的长度固定不变时,应该使用
Nchar 字符类型,同样,这时最多可以存储4000 个字符。当使用 Ntext
数据类型时,该列可以存储多于 4000 个字符。 (4)日期和时间数据类型

if @Dist = 0 
begin 
set @SqlSelect = 'select ' 
set @SqlCounts = 'Count(*)' 
end 
else 
begin 
set @SqlSelect = 'select distinct ' 
set @SqlCounts = 'Count(DISTINCT '+@ID+')' 
end 


if @Sort=0 
begin 
set @strFSortType=' ASC ' 
set @strSortType=' DESC ' 
end 
else 
begin 
set @strFSortType=' DESC ' 
set @strSortType=' ASC ' 
end 



--------生成查询语句-------- 
--此处@strTmp为取得查询结果数量的语句 
if @strCondition is null or @strCondition='' --没有设置显示条件 
begin 
set @sqlTmp = @fldName + ' From ' + @tblName 
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName 
end 
else 
begin 
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition 
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition 
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition 
end 

----取得查询结果总数量----- 
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out 
declare @tmpCounts int 
if @Counts = 0 
set @tmpCounts = 1 
else 
set @tmpCounts = @Counts 

--取得分页总数 
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 

/**//**当前页大于总页数 取最后一页**/ 
if @page>@pageCount 
set @page=@pageCount 

--/*-----数据分页2分处理-------*/ 
declare @pageIndex int --总数/页大小 
declare @lastcount int --总数%页大小 

set @pageIndex = @tmpCounts/@pageSize 
set @lastcount = @tmpCounts%@pageSize 
if @lastcount > 0 
set @pageIndex = @pageIndex + 1 
else 
set @lastcount = @pagesize 

--//***显示分页 
if @strCondition is null or @strCondition='' --没有设置显示条件 
begin 
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 
begin 
if @page=1 
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType 
else 
begin 
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 
+' order by '+ @fldSort +' '+ @strFSortType 
end 
end 
else 
begin 
set @page = @pageIndex-@page+1 --后半部分数据处理 
if @page <= 1 --最后一页数据显示 
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
else 
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
end 
end 

else --有查询条件 
begin 
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 
begin 
if @page=1 
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType 
else 
begin 
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 
+' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType 
end 
end 
else 
begin 
set @page = @pageIndex-@page+1 --后半部分数据处理 
if @page <= 1 --最后一页数据显示 
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
else 
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 
+' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
end 
end 

------返回查询结果----- 
exec sp_executesql @strTmp 
--print @strTmp 
SET NOCOUNT OFF 
GO 

www.9778.com 3

日期和时间数据类型包括 Datetime 和 Smalldatetime两种类型
日期和时间数据类型由有效的日期和时间组成。例如,有效的日期和时间数据包括“4/01/98
12:15:00:00:00 PM”和“1:28:29:15:01AM
8/17/98”。前一个数据类型是日期在前,时间在后一个数据类型是霎时间在前,日期在后。在
Microsoft SQL Server中,日期和时间数据类型包括Datetime 和 Smalldatetime
两种类型时,所存储的日期范围是从 1753 年 1 月 1 日开始,到9999 年12 月
31 日结束(每一个值要求 8 个存储字节)。使用 Smalldatetime
数据类型时,所存储的日期范围是 1900年 1 月 1日 开始,到 2079 年 12 月 31
日结束(每一个值要求 4 个存储字节)。
日期的格式可以设定。设置日期格式的命令如下: Set DateFormat {format |
@format _var| 其中,format | @format_var 是日期的顺序。有效的参数包括
MDY、DMY、YMD、YDM、MYD 和 DYM。在默认情况下,日期格式为MDY。
例如,当执行 Set DateFormat YMD 之后,日期的格式为年 月 日 形式;当执行
Set DateFormat DMY 之后,日期的格式为日 月有年 形式 数字数据类型

怎么在数据库中测试呢?

www.9778.com 4

数字数据只包含数字。数字数据类型包括正数和负数、小数和整数
整数由正整数和负整数组成,例如 39、25、0-2 和 33967。在 Micrsoft SQL
Server 中,整数存储的数据类型是 Int,Smallint和 Tinyint。Int
数据类型存储数据的范围大于 Smallint 数据类型存储数据的范围,而 Smallint
据类型存储数据的范围大于Tinyint 数据类型存储数据的范围。使用 Int
数据狗昔存储数据的范围是从 -2 147 483 648 到 2 147 483 647。使用
Smallint 数据类型时,存储数据的范围从 -32 768 到 32 767。使用Tinyint
数据类型时,存储数据的范围是从0 到255。 精确小娄数据在 SQL Server
中的数据类型是 Decimal 和
Numeric。这种数据所占的存储空间根据该数据的位数后的位数来确定。 在SQL
Server 中,近似小数数据的数据类型是 Float 和
Real。例如,三分之一这个分数记作。3333333,当使用近似数据类型时能准确表示。因此,从系统中检索到的数据可能与存储在该列中数据不完全一样。
货币数据表示正的或者负的货币数量 。

 

从上图可以看出既然可以拿到jan
、feb、mar列的值,后面再做自己的判断就容易了。不再赘述。

在 Microsoft SQL Server 中,货币数据的数据类型是Money 和 Smallmoney
Money数据类型要求 8 个存储字节,Smallmoney 数据类型要求 4 个存储字节。

 

由于学艺不精,上述结论是受别人的启发才得出的,相关链接:

特殊数据类型

[sql] view
plain
copy
print?

下面贴出原作者博文原文(身为一名程序员,你懂的):

特殊数据类型包括前面没有提过的数据类型。特殊的数据类型有3种,即
Timestamp、Bit 和 Uniqueidentifier。 Timestamp 用于表示SQL Server
活动的先后顺序,以二进投影的格式表示。Timestamp
数据与插入数据或者日期和时间没有关系。 Bit 由 1 或者 0
组成。当表示真或者假、ON 或者 OFF 时,使用 Bit
数据类型。例如,询问是否是每一次访问的客户机请求可以存储在这种数据类型的列中。
Uniqueidentifier 由 16
字节的十六进制数字组成,表示一个全局唯一的。当表的记录行要求唯一时,GUID是非常有用。例如,在客户标识号列使用这种数据类型可以区别不同的客户。

  1. declare @pageCount int   
  2. declare @Counts int   
  3. exec [dbo].[proc_ListPageInt] ‘sysobjects’, ‘* ‘, 20,1,@pageCount output,@Counts output,’id’, 0,”,’id’,0  
  4. print @pageCount –这个可有可无   
  5. print @Counts –这个可有可无   

    declare @pageCount int
    declare @Counts int
    exec [dbo].[proc_ListPageInt] ‘sysobjects’, ‘* ‘, 20,1,@pageCount output,@Counts output,’id’, 0,”,’id’,0
    print @pageCount –这个可有可无
    print @Counts –这个可有可无

sp_executesql介绍和使用

2.用户定义的数据类型

执行效果如下:

execute相信大家都用的用熟了,简写为exec,除了用来执行存储过程,一般都用来执行动态Sql 
sp_executesql,sql2005中引入的新的系统存储过程,也是用来处理动态sql的,
如: 
exec sp_executesql @sql, N’@count int out,@id varchar(20)’, @cou out 
,@id 
@sql为拼成的动态sql 
N’@count int out,@id varchar(20)’为拼成的动态sql内的参数列表 
@cou out,@id为为动态sql内参数列表提供值的外部参数列表 

用户定义的数据类型基于在 Microsoft SQL Server
中提供的数据类型。当几个表中必须存储同一种数据类型时,并且为保证这些列有相同的数据类型、长度和可空性时,可以使用用户定义的数据类型。例如,可定义一种称为
postal_code 的数据类型,它基于 Char 数据类型。
当创建用户定义的数据类型时,必须提供三个数:数据类型的名称、所基于的系统数据类型和数据类型的可空性。
创建用户定义的数据类型

 

那么它们之间有什么区别呢? 

创建用户定义的数据类型可以使用 Transact-SQL 语句。系统存储过程
sp_addtype 可以来创建用户定义的数据类型。其语法形式如下: sp_addtype
{type},[,system_data_bype][,null_type] 其中,type
是用户定义的数据类型的名称。system_data_type
是系统提供的数据类型,例如 Decimal、Int、Char 等等。 null_type
表示该数据类型是如何处理空值的,必须使用单引号引起来,例如NULL、NOT
NULL或者NONULL。 例子: Use cust Exec sp_addtype ssn,Varchar(11),Not
Null 创建一个用户定义的数据类型 ssn,其基于的系统数据类型是变长为11
的字符,不允许空。 例子: Use cust Exec sp_addtype
birthday,datetime,Null 创建一个用户定义的数据类型
birthday,其基于的系统数据类型是 DateTime,允许空。 例子: Use master
Exec sp_addtype telephone,varchar(24),Not Null Eexc sp_addtype
fax,varchar(24),Null 创建两个数据类型,即 telephone 和 fax
删除用户定义的数据类型

www.9778.com 5

1,它们之间最大的区别是嵌入式的参数,如下面一个语句 
declare @sql nvarchar(2000) 
declare @id varchar(20) 
set @id=’1′ 
set @sql=’select count(*) from emp where id=’ + @id 
exec @sql 
我想把得到的count(*)传出来,用传统的exec是不好办到的,但是用sp_executesql则很容易就办到了: 
declare @sql nvarchar(2000) 
declare @cou int 
declare @id varchar(20) 
set @id=’1′ 
set @sql=’select @count=count(*) from emp where id=@id’ 
exec sp_executesql @sql, N’@count int out,@id varchar(20)’, @cou out 
,@id 
print @cou 
2.性能 
可以看到,如果用exec,由于每次传入的@id不一样,所以每次生成的@sql就不一样,这样每执行一次Sql2005就必须重新将要执行的动态Sql重新编译一次 
但是sp_executesql则不一样,由于将数值参数化,要执行的动态Sql永远不会变化,只是传入的参数的值在变化,那每次执行的时候就秒用重新编译,速度自然快多了哈! 

当用户定义的数据类型不需要时,可删除。删除用户定义的数据类型的命令是
sp_droptype {type}。 例子: Use master Exec sp_droptype ssn
注意:当表中的列还正在使用用户定义的数据类型时,或者在其上面还绑定有默认或者规则时,这种用户定义的数据类型不能删除。

www.9778.com 6

注意: 
1.sp_executesql要求动态Sql和动态Sql参数列表必须是Nvarchar,比如上个例子的@sql,N’@count
int out,@id
varchar(20)’我记得在sql2005中Varchar也可以的,但是我打了Sp3补丁后就不行了,必须为Nvarchar 
2.动态Sql的参数列表与外部提供值的参数列表顺序必需一致,如: 
N’@count int out,@id varchar(20)’, @cou out,@id 
@count 对应 @cou,@id对应@id 
如果不一致,必须显式标明,如: 
N’@count int out,@id varchar(20)’, @id=@id, @count=@cou out 
3.动态SQl的参数列表与外部提供参数的参数列表参数名可以同名

SQLServer中易混淆的数据类型

分页存储过程二:

SQL Server 中易混淆的数据类型
近来在做数据库设计,有时候真弄不清SQL2000里的数据类型,所以摘了这篇文章。

 

(1)char、varchar、text和nchar、nvarchar、ntext
char和varchar的长度都在1到8000
之间,它们的区别在于char是定长字符数据,而varchar是变长字符数据。所谓定长就是长度固定的,当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充,使长度达到相应的长度;而变长字符数据则不会以空格填充。text存储可变长度的非Unicode数据,最大长度为2^31-1
(2,147,483,647)个字符。

[sql] view
plain
copy
print?

后面三种数据类型和前面的相比,从名称上看只是多了个字母n,它表示存储的是Unicode数据类型的字符。写过程序的朋友对Unicode应该很了解。字符中,英文字符只需要一个字节存储就足够了,但汉字众多,需要两个字节存储,英文与汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到
4000之间。和char、varchar比较:nchar、nvarchar则最多存储4000个字符,不论是英文还是汉字;而char、
varchar最多能存储8000个英文,4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字,较为方便,但在存储英文时数量上有些损失。

  1. USE [JianKunKingTestDatabase001]   
  2. GO   
  3.   
  4. /****** Object: StoredProcedure [dbo].[A_P_HelpPageShow] Script Date: 01/21/2015 19:19:42 ******/   
  5. SET ANSI_NULLS ON   
  6. GO   
  7.   
  8. SET QUOTED_IDENTIFIER ON   
  9. GO   
  10.   
  11.   
  12. ALTER PROCEDURE [dbo].[A_P_HelpPageShow]   
  13. (   
  14. @tblName nvarchar(max), — 表名   
  15. @strGetFields varchar(1000) = ‘*’, — 需要返回的列   
  16. @fldName varchar(255)=”, — 排序的字段名   
  17. @PageSize int = 10, — 页尺寸   
  18. @PageIndex int = 1, — 页码   
  19. @OrderType bit = 0, — 设置排序类型, 非 0 值则降序   
  20. @strWhere varchar(1500) = ”, — 查询条件 (注意: 不要加 where)   
  21. @Counts int = 0 output –查询到的记录数   
  22. )   
  23. AS   
  24. declare @strSQL nvarchar(4000) — 主语句   
  25. declare @strTmp nvarchar(110) — 临时变量   
  26. declare @strOrder nvarchar(400) — 排序类型   
  27. declare @totalRecord int –查询到的记录数   
  28. declare @SqlCounts nvarchar(max) —-对总数查询进行SQL构造   
  29. –计算总记录数   
  30. begin   
  31. if @strWhere !=”   
  32. set @SqlCounts = ‘select @totalRecord=count(*) from ‘ + @tblName + ‘ where ‘+@strWhere  
  33. else   
  34. set @SqlCounts = ‘select @totalRecord=count(*) from ‘ + @tblName + ”   
  35. end   
  36.   
  37. –print @strWhere   
  38. –print @SqlCounts   
  39. exec sp_executesql @SqlCounts,N’@totalRecord int OUTPUT’,@totalRecord OUTPUT–计算总记录数   
  40. set @Counts=@totalRecord   
  41.   
  42. begin   
  43.   
  44. if @OrderType != 0   
  45. begin   
  46. set @strTmp = ‘<(select min’   
  47. set @strOrder = ‘ order by ‘ + @fldName +’ desc’   
  48. –如果@OrderType不是0,就执行降序,这句很重要!   
  49. end   
  50. else   
  51. begin   
  52. set @strTmp = ‘>(select max’   
  53. set @strOrder = ‘ order by ‘ + @fldName +’ asc’   
  54. end   
  55. –print @strOrder   
  56. if @PageIndex = 1   
  57. begin   
  58. if @strWhere != ”   
  59. set @strSQL = ‘select top ‘ + str(@PageSize) +’ ‘+@strGetFields+ ‘ from ‘ + @tblName + ‘ where ‘ + @strWhere + ‘ ‘ + @strOrder   
  60. else   
  61. set @strSQL = ‘select top ‘ + str(@PageSize) +’ ‘+@strGetFields+ ‘ from ‘+ @tblName + ‘ ‘+ @strOrder   
  62. –如果是第一页就执行以上代码,这样会加快执行速度   
  63. end   
  64. else   
  65. begin   
  66. –以下代码赋予了@strSQL以真正执行的SQL代码   
  67. set @strSQL = ‘select top ‘ + str(@PageSize) +’ ‘+@strGetFields+ ‘ from ‘   
  68. + @tblName + ‘ where ‘ + @fldName + ‘ ‘ + @strTmp + ‘(‘+ @fldName + ‘) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ ‘+ @fldName + ‘ from ‘ + @tblName + ‘ ‘ + @strOrder + ‘) as tblTmp)’+ @strOrder   
  69. –print @strSQL   
  70. if @strWhere != ”   
  71. set @strSQL = ‘select top ‘ + str(@PageSize) +’ ‘+@strGetFields+ ‘ from ‘   
  72. + @tblName + ‘ where ‘ + @fldName + ‘ ‘ + @strTmp + ‘(‘   
  73. + @fldName + ‘) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ ‘   
  74. + @fldName + ‘ from ‘ + @tblName + ‘ where ‘ + @strWhere + ‘ ‘   
  75. + @strOrder + ‘) as tblTmp) and ‘ + @strWhere + ‘ ‘ + @strOrder   
  76. end   
  77. end   
  78. –print @strSQL   
  79. exec sp_executesql @strSQL   
  80. GO   

    USE [JianKunKingTestDatabase001]
    GO

    /** Object: StoredProcedure [dbo].[A_P_HelpPageShow] Script Date: 01/21/2015 19:19:42 **/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

(2)datetime和smalldatetime
datetime:从1753年1月1日到9999年12月31日的日期和时间数据,精确到百分之三秒。
smalldatetime:从1900年1月1日到2079年6月6日的日期和时间数据,精确到分钟。

ALTER PROCEDURE [dbo].[A_P_HelpPageShow] 
( 
@tblName nvarchar(max), -- 表名 
@strGetFields varchar(1000) = '*', -- 需要返回的列 
@fldName varchar(255)='', -- 排序的字段名 
@PageSize int = 10, -- 页尺寸 
@PageIndex int = 1, -- 页码 
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where) 
@Counts int = 0 output --查询到的记录数 
) 
AS 
declare @strSQL nvarchar(4000) -- 主语句 
declare @strTmp nvarchar(110) -- 临时变量 
declare @strOrder nvarchar(400) -- 排序类型 
declare @totalRecord int --查询到的记录数 
declare @SqlCounts nvarchar(max) ----对总数查询进行SQL构造 
--计算总记录数 
begin 
if @strWhere !='' 
set @SqlCounts = 'select @totalRecord=count(*) from ' + @tblName + ' where '+@strWhere
else 
set @SqlCounts = 'select @totalRecord=count(*) from ' + @tblName + '' 
end 

--print @strWhere 
--print @SqlCounts 
exec sp_executesql @SqlCounts,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 
set @Counts=@totalRecord 

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 
--print @strOrder 
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 
--print @strSQL 
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 
--print @strSQL 
exec sp_executesql @strSQL 
GO 

(3)bitint、int、smallint、tinyint和bit
bigint:从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据。
int:从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据。
smallint:从-2^15(-32,768)到2^15-1(32,767)的整数数据。
tinyint:从0到255的整数数据。 bit:1或0的整数数据。

怎么在数据库中测试呢?

(4)decimal和numeric
这两种数据类型是等效的。都有两个参数:p和s。p指定小数点左边和右边可以存储的十进制数字的最大个数,p必须是从
1到38之间的值。s指定小数点右边可以存储的十进制数字的最大个数,s必须是从0到p之间的值,默认小数位数是0。

 

(5)float和real float:从-1.79^308到1.79^308之间的浮点数字数据。
real:从-3.40^38到3.40^38之间的浮点数字数据。在SQL
Server中,real的同义词为float(24)。

 

[sql] view
plain
copy
print?

  1. select count(*) from sysobjects ;  
  2. declare @CountsAA int   
  3. exec [dbo].[A_P_HelpPageShow] ‘sysobjects’, ‘* ‘, ‘id ‘,20, 1,1, ‘ ‘,@CountsAA output   
  4. print @CountsAA –这个可有可无  

    select count() from sysobjects ;
    declare @CountsAA int
    exec [dbo].[A_P_HelpPageShow] ‘sysobjects’, ‘
    ‘, ‘id ‘,20, 1,1, ‘ ‘,@CountsAA output
    print @CountsAA –这个可有可无

执行结果如下:

 

www.9778.com 7

www.9778.com 8

存储过程二(优化版)

 

[sql] view
plain
copy
print?

  1. USE [JianKunKingTestDatabase001]  
  2. GO  
  3.   
  4. /****** Object:  StoredProcedure [dbo].[A_P_HelpPageShow]    Script Date: 01/30/2015 20:21:13 ******/  
  5. SET ANSI_NULLS ON  
  6. www.9778.com,GO  
  7.   
  8. SET QUOTED_IDENTIFIER ON  
  9. GO  
  10.   
  11.   
  12. CREATE  PROCEDURE [dbo].[A_P_HelpPageShow]  
  13. (  
  14. @TableName   nvarchar(max),       — 表名  
  15. @strGetFields varchar(1000) = ‘*’, — 需要返回的列   
  16. @OrderField varchar(255)=”,      — 排序的字段名  
  17. @PageSize   int = 10,          — 页尺寸  
  18. @PageIndex int = 1,          — 页码  
  19. @strWhere varchar(1500) = ”, — 查询条件 (注意: 不要加 where)  
  20. @Counts    int = 0  output  –查询到的记录数  
  21. )  
  22. AS  
  23. declare @strSQL   nvarchar(4000)       — 主语句  
  24. declare @totalRecord int  –查询到的记录数  
  25. declare @SqlCounts nvarchar(max)     —-对总数查询进行SQL构造  
  26. –计算总记录数  
  27. begin  
  28.     if @strWhere !=”  
  29. set @SqlCounts = ‘select @totalRecord=count(*)  from ‘ + @TableName + ‘ where ‘+@strWhere  
  30.     else  
  31. set @SqlCounts = ‘select @totalRecord=count(*)  from ‘ + @TableName + ”   
  32. end   
  33.   
  34. exec sp_executesql @SqlCounts,N’@totalRecord int OUTPUT’,@totalRecord OUTPUT–计算总记录数  
  35. set  @Counts=@totalRecord   
  36.   
  37. BEGIN  
  38. IF (@strWhere=” or @strWhere IS NULL)  
  39. SET @strSQL = ‘Select * FROM (select ‘ + @strGetFields + ‘,ROW_NUMBER() Over(order by ‘ + @OrderField + ‘) as rowId from ‘ + @TableName  
  40. ELSE  
  41. SET @strSQL = ‘Select * FROM (select ‘ + @strGetFields + ‘,ROW_NUMBER() Over(order by ‘ + @OrderField + ‘) as rowId from ‘ + @TableName + ‘ where ‘ + @strWhere      
  42. END  
  43.  –处理页数超出范围情况   
  44.     IF @PageIndex<=0   
  45.         SET @PageIndex = 1  
  46.         
  47.      –处理开始点和结束点  
  48.     DECLARE @StartRecord INT   
  49.     DECLARE @EndRecord int  
  50.       
  51.     SET @StartRecord = (@pageIndex-1)*@PageSize + 1  
  52.     SET @EndRecord = @StartRecord + @PageSize – 1  
  53.   
  54.      –继续合成sql语句  
  55.     SET @strSQL = @strSQL + ‘) as tempTable where rowId >=’ + CONVERT(VARCHAR(50),@StartRecord) + ‘ and rowid<= ‘ + CONVERT(VARCHAR(50),@EndRecord)  
  56.   
  57. exec sp_executesql @strSQL  
  58.   
  59.   
  60. GO  

    USE [JianKunKingTestDatabase001]
    GO

    /** Object: StoredProcedure [dbo].[A_P_HelpPageShow] Script Date: 01/30/2015 20:21:13 **/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

CREATE  PROCEDURE [dbo].[A_P_HelpPageShow]
(
@TableName   nvarchar(max),       -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列 
@OrderField varchar(255)='',      -- 排序的字段名
@PageSize   int = 10,          -- 页尺寸
@PageIndex int = 1,          -- 页码
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@Counts    int = 0  output  --查询到的记录数
)
AS
declare @strSQL   nvarchar(4000)       -- 主语句
declare @totalRecord int  --查询到的记录数
declare @SqlCounts nvarchar(max)     ----对总数查询进行SQL构造
--计算总记录数
begin
    if @strWhere !=''
set @SqlCounts = 'select @totalRecord=count(*)  from ' + @TableName + ' where '+@strWhere
    else
set @SqlCounts = 'select @totalRecord=count(*)  from ' + @TableName + '' 
end 

exec sp_executesql @SqlCounts,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
set  @Counts=@totalRecord 

BEGIN
IF (@strWhere='' or @strWhere IS NULL)
SET @strSQL = 'Select * FROM (select ' + @strGetFields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
ELSE
SET @strSQL = 'Select * FROM (select ' + @strGetFields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @strWhere    
END
 --处理页数超出范围情况 
    IF @PageIndex<=0 
        SET @PageIndex = 1

     --处理开始点和结束点
    DECLARE @StartRecord INT 
    DECLARE @EndRecord int

    SET @StartRecord = (@pageIndex-1)*@PageSize + 1
    SET @EndRecord = @StartRecord + @PageSize - 1

     --继续合成sql语句
    SET @strSQL = @strSQL + ') as tempTable where rowId >=' + CONVERT(VARCHAR(50),@StartRecord) + ' and rowid<= ' + CONVERT(VARCHAR(50),@EndRecord)

exec sp_executesql @strSQL


GO

拓展: