修改的一个分页存储过程
帮同事修改的一个存储过程,是今年来第一次写分页,一年过的真快,数据库今年学习的太慢,感觉有点对不起自己,继续努力吧 !
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,liuyong>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE ProjectList
@PageSize int, --页大小
@PageIndex int, --第几页
@Count int output, -- 返回条数
@Condition varchar(4000) --where条件
AS
BEGIN
PRINT @Condition
-- 查询总数
--在这里判断条件
DECLARE @str NVARCHAR(max)
--pro.Project_Status=3 and pro.Project_Startime>'2010-12-24'
SET @str = 'SELECT @total = count(Project_ID)
FROM (
SELECT
pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
min(price.PI_Price) as minPrice ,
max(price.PI_Price)as maxPrice,
pro.Project_ImportentStatus
from dbo.TB_Project_Info as pro
inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
where 1=1 AND '+@Condition+'
pro.ProjectKind_ID in(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
) AS s '
exec sp_executesql @str,N'@total int output ',@Count OUTPUT;
--测试,不用去掉
select @Count
PRINT @str;
-- SELECT @Count= count(Project_ID)
-- FROM (
-- SELECT
-- pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
-- pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
-- min(price.PI_Price) as minPrice ,
-- max(price.PI_Price)as maxPrice,
-- pro.Project_ImportentStatus
-- from dbo.TB_Project_Info as pro
-- inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
-- left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
-- where @Condition+''+
-- --pro.Project_Status=3 and pro.Project_Startime>'2010-12-24'
-- pro.ProjectKind_ID in(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
-- group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
-- ) AS s
-- 判断条件不为空,执行分页
IF @Condition IS NOT NULL
BEGIN
-- 计算分页
-- select * FROM (
-- SELECT ROW_NUMBER() OVER (ORDER BY pro.Project_ID) AS row_id,
-- pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
-- pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
-- min(price.PI_Price) as minPrice ,
-- max(price.PI_Price)as maxPrice,
-- pro.Project_ImportentStatus
-- from dbo.TB_Project_Info as pro
-- inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
-- left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
-- where @Condition+''+
--
-- pro.ProjectKind_ID IN(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
-- group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
-- ) AS s WHERE s.row_id BETWEEN @PageSize * ( @PageIndex-1) AND @PageSize * ( @PageIndex-1)+@PageSize;
SET @str='select * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY pro.Project_ID) AS row_id,
pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
min(price.PI_Price) as minPrice ,
max(price.PI_Price)as maxPrice,
pro.Project_ImportentStatus
from dbo.TB_Project_Info as pro
inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
where 1=1 and '+@Condition+'
pro.ProjectKind_ID IN(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
) AS s WHERE s.row_id BETWEEN '+ CAST(@PageSize AS NVARCHAR)+' * ( '+CAST(@PageIndex AS NVARCHAR)+'-1) AND '+CAST(@PageSize AS NVARCHAR)+' * ('+CAST(@PageIndex AS NVARCHAR)+'-1)+'+CAST(@PageSize AS NVARCHAR)+'';
PRINT @str
end
EXECUTE(@str);
END
GO
SELECT CONVERT(NVARCHAR ,'2011-11-11',102)
SELECT CAST('2011-11-11' AS nvarchar) --时间
SELECT CAST('100' AS nvarchar) --
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,liuyong>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE ProjectList
@PageSize int, --页大小
@PageIndex int, --第几页
@Count int output, -- 返回条数
@Condition varchar(4000) --where条件
AS
BEGIN
PRINT @Condition
-- 查询总数
--在这里判断条件
DECLARE @str NVARCHAR(max)
--pro.Project_Status=3 and pro.Project_Startime>'2010-12-24'
SET @str = 'SELECT @total = count(Project_ID)
FROM (
SELECT
pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
min(price.PI_Price) as minPrice ,
max(price.PI_Price)as maxPrice,
pro.Project_ImportentStatus
from dbo.TB_Project_Info as pro
inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
where 1=1 AND '+@Condition+'
pro.ProjectKind_ID in(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
) AS s '
exec sp_executesql @str,N'@total int output ',@Count OUTPUT;
--测试,不用去掉
select @Count
PRINT @str;
-- SELECT @Count= count(Project_ID)
-- FROM (
-- SELECT
-- pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
-- pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
-- min(price.PI_Price) as minPrice ,
-- max(price.PI_Price)as maxPrice,
-- pro.Project_ImportentStatus
-- from dbo.TB_Project_Info as pro
-- inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
-- left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
-- where @Condition+''+
-- --pro.Project_Status=3 and pro.Project_Startime>'2010-12-24'
-- pro.ProjectKind_ID in(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
-- group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
-- ) AS s
-- 判断条件不为空,执行分页
IF @Condition IS NOT NULL
BEGIN
-- 计算分页
-- select * FROM (
-- SELECT ROW_NUMBER() OVER (ORDER BY pro.Project_ID) AS row_id,
-- pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
-- pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
-- min(price.PI_Price) as minPrice ,
-- max(price.PI_Price)as maxPrice,
-- pro.Project_ImportentStatus
-- from dbo.TB_Project_Info as pro
-- inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
-- left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
-- where @Condition+''+
--
-- pro.ProjectKind_ID IN(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
-- group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
-- ) AS s WHERE s.row_id BETWEEN @PageSize * ( @PageIndex-1) AND @PageSize * ( @PageIndex-1)+@PageSize;
SET @str='select * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY pro.Project_ID) AS row_id,
pro.Project_LitterPicture, pro.Project_ID, pro.Project_Name,pro.VanueName,
pro.Project_Status, pro.Project_Startime,pro.Project_EndTime,
min(price.PI_Price) as minPrice ,
max(price.PI_Price)as maxPrice,
pro.Project_ImportentStatus
from dbo.TB_Project_Info as pro
inner join TB_ProjectDetail_Info as cc on cc.Project_ID=pro.Project_ID
left join dbo.TB_Price_Info as price on price.PD_ID=cc.PD_ID
where 1=1 and '+@Condition+'
pro.ProjectKind_ID IN(select ProjectKind_ID from TB_ProjectKind_Info where ProjectKindParent_id= 1)
group by pro.Project_ID, pro.Project_Name,pro.VanueName,pro.Project_Status, pro.Project_Startime,pro.Project_EndTime , pro.Project_ImportentStatus,pro.Project_LitterPicture
) AS s WHERE s.row_id BETWEEN '+ CAST(@PageSize AS NVARCHAR)+' * ( '+CAST(@PageIndex AS NVARCHAR)+'-1) AND '+CAST(@PageSize AS NVARCHAR)+' * ('+CAST(@PageIndex AS NVARCHAR)+'-1)+'+CAST(@PageSize AS NVARCHAR)+'';
PRINT @str
end
EXECUTE(@str);
END
GO
SELECT CONVERT(NVARCHAR ,'2011-11-11',102)
SELECT CAST('2011-11-11' AS nvarchar) --时间
SELECT CAST('100' AS nvarchar) --
出处:http://www.cnblogs.com/liuyong/
作者喜欢研究 Sql Server ,ASP.NET MVC , Jquery WCF 等技术,同时关心分布式架构的设计应用。转载请保留原文链接,谢谢!