CREATE PROCEDURE PageGridData
@tblName varchar(250), -- 表名
@strGetFields varchar(2000) = '*', -- 需要返回的列
@fldKeyName varchar(255)='', -- 主关键字
@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(3000) -- 主语句
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,就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType = -1
begin
set @strTmp = " not in (select "
set @strOrder = " order by " + @fldName
--如果@OrderType是-1,自定义排序
end
else
if @OrderType != 0
begin
set @strTmp = " not in (select "
set @strOrder = " order by " + @fldName +" desc"
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = " not in (select "
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 " + @fldKeyName + "" + @strTmp + "("+ @fldKeyName + ") from (select top " + str((@PageIndex-1)*@PageSize) + " "+ @fldKeyName + " from " + @tblName + "" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from "
+ @tblName + " where " + @fldKeyName + "" + @strTmp + "("
+ @fldKeyName + ") from (select top " + str((@PageIndex-1)*@PageSize) + " "
+ @fldKeyName + " from " + @tblName + " where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
print (@strSQL)
exec (@strSQL)
GO
这是个通用分页存储过程(稍修改下)
现在我有数据源SELECT a.*,(SELECT TOP 1 Specifications FROM Data_Resource WHERE ResourceUniformID = a.ResourceUniformID AND ResourceUniformName = a.ResourceUniformName) AS Specifications into #table
FROM(
SELECT DISTINCT ResourceUniformID,ResourceUniformName,b.UniformUnit,c.Name AS UniformUnitName
FROM jzs_Resource a
INNER JOIN Data_Resource b ON a.ResourceID = b.ResourceID AND a.ResourceName = b.ResourceName
LEFT OUTER JOIN jzs_UnitList c ON b.UniformUnit = c.Code
WHERE ProjectCode IN(SELECT ProjectCode FROM jzs_ChildProject WHERE FatherProjectCode = '参数'
)AND a.stuffType = ''1'' AND ResourceUniformID IS NOT NULL AND ResourceUniformName IS NOT NULL ) AS a
请问怎么把这个数据源作为TABLE名 使用这个通用存储过程进行分页呢
现在我用的 不是这个分页
是不是把这段SQL 作为TABLE 名整个传进去呢 还是有别的方法。
请高手指点
主要是SQL 嵌套SQL 不知道怎么弄了
@tblName varchar(250), -- 表名
@strGetFields varchar(2000) = '*', -- 需要返回的列
@fldKeyName varchar(255)='', -- 主关键字
@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(3000) -- 主语句
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,就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType = -1
begin
set @strTmp = " not in (select "
set @strOrder = " order by " + @fldName
--如果@OrderType是-1,自定义排序
end
else
if @OrderType != 0
begin
set @strTmp = " not in (select "
set @strOrder = " order by " + @fldName +" desc"
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = " not in (select "
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 " + @fldKeyName + "" + @strTmp + "("+ @fldKeyName + ") from (select top " + str((@PageIndex-1)*@PageSize) + " "+ @fldKeyName + " from " + @tblName + "" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from "
+ @tblName + " where " + @fldKeyName + "" + @strTmp + "("
+ @fldKeyName + ") from (select top " + str((@PageIndex-1)*@PageSize) + " "
+ @fldKeyName + " from " + @tblName + " where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
print (@strSQL)
exec (@strSQL)
GO
这是个通用分页存储过程(稍修改下)
现在我有数据源SELECT a.*,(SELECT TOP 1 Specifications FROM Data_Resource WHERE ResourceUniformID = a.ResourceUniformID AND ResourceUniformName = a.ResourceUniformName) AS Specifications into #table
FROM(
SELECT DISTINCT ResourceUniformID,ResourceUniformName,b.UniformUnit,c.Name AS UniformUnitName
FROM jzs_Resource a
INNER JOIN Data_Resource b ON a.ResourceID = b.ResourceID AND a.ResourceName = b.ResourceName
LEFT OUTER JOIN jzs_UnitList c ON b.UniformUnit = c.Code
WHERE ProjectCode IN(SELECT ProjectCode FROM jzs_ChildProject WHERE FatherProjectCode = '参数'
)AND a.stuffType = ''1'' AND ResourceUniformID IS NOT NULL AND ResourceUniformName IS NOT NULL ) AS a
请问怎么把这个数据源作为TABLE名 使用这个通用存储过程进行分页呢
现在我用的 不是这个分页
是不是把这段SQL 作为TABLE 名整个传进去呢 还是有别的方法。
请高手指点
主要是SQL 嵌套SQL 不知道怎么弄了
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货