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)今天一天都在弄这个存储过程,也没调试通过...
麻烦大家了,帮改一下...可能有点多。
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)今天一天都在弄这个存储过程,也没调试通过...
麻烦大家了,帮改一下...可能有点多。
解决方案 »
- 这段SQL语句错了吗?怎么会提示这个??
- 对于membership的疑问?
- 不包含“SaveChanges”的定义,SaveChanges缺少using。谁知道什么原因
- 我有一个小问题帮点忙啊!
- 来自低层ASP。NET程序员的困惑
- ASP.NET下用AJAX支不支持SESSION?
- 使用代理后出现 无法开始调试,无法找到 Microsoft Internet Explorer
- 很奇怪的常规网络错误,请检查网络文档!
- asp.net里面为什么要用存储过程???
- 在读取数据库的时候如何弹出一个等待的画面!!!--waiting!!
- 还有什么是和ewebeditor类似的控件吗
- asp.net浏览器怎么关闭
直接把WHERE Row between @startIndex and @endIndex
接到@sql2后面,就行了啊
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)
beginSET @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
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)
beginSET @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