【分页存储过程】又想实现不定条件的查询，遇到点问题，麻烦大家想想办法....

[sunlovesea]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [SCE].[Test]
(@startIndex INT=null, @endindex INT=null,@docount bit=null,@ShiftId int=null,@TeamId int=null,@Dt datetime=null)
AS
set nocount on
if(@docount=1)
BEGIN 
	DECLARE @sql varchar(4000)
	SET @sql='select count(sm.Id) from sce.MaterailBack sm WHERE 1=1'
IF (@ShiftId!=NULL) SET @sql=@sql+' AND sm.ShiftId=@ShiftId'
IF(@TeamId!=NULL) SET @sql=@sql+' AND sm.TeamId=@TeamId'
IF (@Dt!=NULL) SET @sql=@sql+' AND sm.Dt=@Dt'
EXEC (@sql)	 
END
ELSE
BEGIN 
	DECLARE @sql2 varchar(4000)
	SET @sql2='SELECT ROW_NUMBER() OVER (ORDER BY sm.Id DESC)AS Row, sm.*,ps.NM shiftNM,pu.NM unitNM,puser.NM userNM from sce.MaterailBack sm Where 1=1' 
	IF (@ShiftId!=NULL) SET @sql2=@sql2+'AND sm.ShiftId=@ShiftId'	
	IF(@TeamId!=NULL) SET @sql2=@sql2+'AND sm.TeamId=@TeamId'
	IF (@Dt!=NULL) SET @sql2=@sql2+'AND sm.Dt=@Dt'
	EXEC (@sql2)
WITH tbTemp AS 
(
  --这个地方由于With的限制
  --不能执行Exec（Sql2）	
)	
SELECT *
FROM tbTemp
WHERE Row between @startIndex and @endIndex    --分页查询
END 
set nocount OFF

如上写的存储过程，利用Where 1=1 实现不定条件的查询，又想实现分页。但是在With块内，不能执行Exec（Sql2）

今天一天都在弄这个存储过程，也没调试通过...
麻烦大家了,帮改一下...可能有点多。

[sjt000]

你这个完全可以不用with啊～～
直接把WHERE Row between @startIndex and @endIndex
接到@sql2后面，就行了啊

[pgameli]

看的头晕...
友情帮顶

[wujinjian2008n]

[Quote=引用 2 楼 pgameli 的回复:]
看的头晕...
友情帮顶
[/Quote]

[zzxap]

[code=SQL]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [SCE].[Test]
(@startIndex INT=null, @endindex INT=null,@docount bit=null,@ShiftId int=null,@TeamId int=null,@Dt datetime=null,@pagesize int,@CurrentPage )
--@pagesize 每页显示的条数 ,@CurrentPage 当前页
AS
set nocount on
if(@docount=1)
BEGIN
DECLARE @sql varchar(4000)
SET @sql='select count(sm.Id) from sce.MaterailBack sm WHERE 1=1'
IF (@ShiftId!=NULL) SET @sql=@sql+' AND sm.ShiftId=@ShiftId'
IF(@TeamId!=NULL) SET @sql=@sql+' AND sm.TeamId=@TeamId'
IF (@Dt!=NULL) SET @sql=@sql+' AND sm.Dt=@Dt'
EXEC (@sql)
END
ELSE
BEGIN
DECLARE @sql2 varchar(4000)
SET @sql2='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY sm.Id DESC)AS Row, sm.*,ps.NM shiftNM,pu.NM unitNM,puser.NM userNM from sce.MaterailBack sm Where 1=1 )A where 1=1 '

IF (@ShiftId!=NULL)
begin
SET @sql2=@sql2+'AND sm.ShiftId=@ShiftId'
end
IF(@TeamId!=NULL)
begin
SET @sql2=@sql2+'AND sm.TeamId=@TeamId'
end
IF (@Dt!=NULL)
begin

SET @sql2=@sql2+'AND sm.Dt=@Dt'
end

@sql2=@sql2+'and A.Row between cast(@pagesize as int) *(cast(@ CurrentPage as int)-1)+1 and cast(@PageSize as int)*cast(@ CurrentPage as int)'

EXEC (@sql2)

END
set nocount OFF

[/CODE]

[zzxap]

[code=SQL]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [SCE].[Test]
(@startIndex INT=null, @endindex INT=null,@docount bit=null,@ShiftId int=null,@TeamId int=null,@Dt datetime=null,@pagesize int,@CurrentPage int)
--@pagesize 每页显示的条数 ,@CurrentPage 当前页
AS
set nocount on
if(@docount=1)
BEGIN
DECLARE @sql varchar(8000)
SET @sql='select count(sm.Id) from sce.MaterailBack sm WHERE 1=1'
IF (@ShiftId!=NULL) SET @sql=@sql+' AND sm.ShiftId=@ShiftId'
IF(@TeamId!=NULL) SET @sql=@sql+' AND sm.TeamId=@TeamId'
IF (@Dt!=NULL) SET @sql=@sql+' AND sm.Dt=@Dt'
EXEC (@sql)
END
ELSE
BEGIN
DECLARE @sql2 varchar(4000)
SET @sql2='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY sm.Id DESC)AS Row, sm.*,ps.NM shiftNM,pu.NM unitNM,puser.NM userNM from sce.MaterailBack sm Where 1=1 )A where 1=1 '

IF (@ShiftId!=NULL)
begin
SET @sql2=@sql2+'AND sm.ShiftId=@ShiftId'
end
IF(@TeamId!=NULL)
begin
SET @sql2=@sql2+'AND sm.TeamId=@TeamId'
end
IF (@Dt!=NULL)
begin

SET @sql2=@sql2+'AND sm.Dt=@Dt'
end

@sql2=@sql2+'and A.Row between cast(@pagesize as int) *(cast(@ CurrentPage as int)-1)+1 and cast(@PageSize as int)*cast(@ CurrentPage as int)'

EXEC (@sql2)

END
set nocount OFF

[/CODE]

[hhxxcj]

楼上的就行了，不用WITH 也可以地

[zzxap]

齐人结贴