if @sql2 is not null and @sql2 <> ''
begin
if @sql3 is not null and @sql3 <> ''
begin
set @strSql = @strSql +' '+'and indate between'''+cast(@sql2 as NVarChar)+'''' + 'and'''+cast(@sql3 as NVarChar)+''''
end
else
begin
set @strSql = @strSql +' '+'and indate='''+cast(@sql2 as NVarChar)+''''
end
end
如果这么写,那么当点击到某个页数的时候就显示同样的数据,也就是比如当你点击第7页的时候老显示第6页的数据,点第8,9,等后面的页数也是显示第6页的数据,开始1-5页都能正确显示,那么请教上面的代码怎么改?上面的是2个时间输入框传来的参数,判断规则是:第1个和第2个输入框都有可能为空,或者第1个不为空,而第2个为空,或者第1个空,而第2个不为空还有,如果是一个输入框,那么下面这么写也正确:if @sql5 is null or @sql5=''
set @strSql = @strSql + ''
else
begin
set @strSql = @strSql + ' ' + 'and depno='''+cast(@sql5 as NVarChar)+''''
end如果可以还帮我改下面的代码:if @sql1 is not null and @sql1 <> ''
begin
if @sql1=7
begin
set @strSql = @strSql +' '+ 'and status <> 5'
end
else
begin
set @strSql = @strSql +' '+ 'and status='+str(@sql1)
end
end这个也是如上错误这是个单选框,判断规则是:如果为7,就显示不为5的所有数据,其他的是点1就显示条件为1的数据,点2就显示条件为2的数据
begin
if @sql3 is not null and @sql3 <> ''
begin
set @strSql = @strSql +' '+'and indate between'''+cast(@sql2 as NVarChar)+'''' + 'and'''+cast(@sql3 as NVarChar)+''''
end
else
begin
set @strSql = @strSql +' '+'and indate='''+cast(@sql2 as NVarChar)+''''
end
end
如果这么写,那么当点击到某个页数的时候就显示同样的数据,也就是比如当你点击第7页的时候老显示第6页的数据,点第8,9,等后面的页数也是显示第6页的数据,开始1-5页都能正确显示,那么请教上面的代码怎么改?上面的是2个时间输入框传来的参数,判断规则是:第1个和第2个输入框都有可能为空,或者第1个不为空,而第2个为空,或者第1个空,而第2个不为空还有,如果是一个输入框,那么下面这么写也正确:if @sql5 is null or @sql5=''
set @strSql = @strSql + ''
else
begin
set @strSql = @strSql + ' ' + 'and depno='''+cast(@sql5 as NVarChar)+''''
end如果可以还帮我改下面的代码:if @sql1 is not null and @sql1 <> ''
begin
if @sql1=7
begin
set @strSql = @strSql +' '+ 'and status <> 5'
end
else
begin
set @strSql = @strSql +' '+ 'and status='+str(@sql1)
end
end这个也是如上错误这是个单选框,判断规则是:如果为7,就显示不为5的所有数据,其他的是点1就显示条件为1的数据,点2就显示条件为2的数据
解决方案 »
- htmlfile: 未知的运行时错误
- 这样的报表如何做呀?
- 怎样才能用textbox作为输入框,在页面上连续输入一个班学生的某科成绩?
- 请问各位:DataGrid,DropDownList用控件sqlConnection,dataSet,sqlDataAdapter如何绑定数据库数据?能实现吗?
- .net中我在数据层的类里写了很多sql语句,可是,我怎么输出这些sql语句呢?
- 如何用程序自动将一文本格式的文件中的特定数据群导入数据库?最好用部分代码解答,谢谢!
- 路径问题, 网页超链接路径设置,请高手,解决后马上结贴.
- 能否提供一个ewebeditor+ASP.NET代码的例子?
- help !!!!!!!!!!!!help me !!!!!how can help me
- xml 是描述数据的, 如何解释更加清楚 这个概念? 大家怎样编写 xslt ? schema 呢?
- 求DATALIST控件下的分页存储,最好带有传查询条件参数的存储?
- 存储错误:例外詳細資訊: 沒有包含任何資料來源。
CREATE PROCEDURE p_GetPayList
(
@FromTime nvarchar(20) = null,
@ToTime nvarchar(20) = null,
@pagesize int=20,--分页大小
@pageindex int=1,--当前页
@paytype int=2,--支付类型,0为收入1为支出,默认2为显示所有类型(特用于报表)3也为显示所有类型
@docount bit=0,--是否统计总条数
@userid int=0--显示特定用户的数据,默认0为显示所有
)
AS
set nocount on
if(@paytype=2)--显示报表
begin
if (@FromTime is null and @ToTime is null)
select *,username=(SELECT username FROM t_user WHERE userid=t_paylist.userid) from t_paylist where paystate=1
else
select *,username=(SELECT username FROM t_user WHERE userid=t_paylist.userid) from t_paylist where paystate=1 and paytime between @fromtime and dateadd(day,1,@totime)
end
else
begin
if(@docount=1)
begin
if(@paytype=3)--显示所有类型
begin
if(@userid=0)
begin
if (@FromTime is null and @ToTime is null)
SELECT COUNT(balanceid) FROM t_paylist
else
SELECT COUNT(balanceid) FROM t_paylist WHERE balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime))
end
else
begin
if (@FromTime is null and @ToTime is null)
SELECT COUNT(balanceid) FROM t_paylist WHERE userid=@userid
else
SELECT COUNT(balanceid) FROM t_paylist WHERE userid=@userid AND balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime))
end
end
else--显示特定类型
begin
if(@userid=0)
begin
if (@FromTime is null and @ToTime is null)
SELECT COUNT(balanceid) FROM t_paylist WHERE paytype=@paytype
else
SELECT COUNT(balanceid) FROM t_paylist WHERE paytype=@paytype AND balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime))
end
else
begin
if (@FromTime is null and @ToTime is null)
SELECT COUNT(balanceid) FROM t_paylist WHERE paytype=@paytype and userid=@userid
else
SELECT COUNT(balanceid) FROM t_paylist WHERE paytype=@paytype and userid=@userid AND balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime))
end
end
end
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
if(@paytype=3)--所有类型
begin
if(@userid=0)--所有用户
begin
if (@FromTime is null and @ToTime is null)
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist order by balanceid desc
else
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist WHERE balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime)) order by balanceid desc
end
else
begin
if (@FromTime is null and @ToTime is null)
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist WHERE userid=@userid order by balanceid desc
else
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist WHERE userid=@userid AND balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime)) order by balanceid desc
end
end
else--特定类型
begin
if(@userid=0)--所有用户
begin
if (@FromTime is null and @ToTime is null)
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist WHERE paytype=@paytype order by balanceid desc
else
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist WHERE paytype=@paytype AND balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime)) order by balanceid desc
end
else
begin
if (@FromTime is null and @ToTime is null)
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist WHERE paytype=@paytype and userid=@userid order by balanceid desc
else
INSERT INTO @indextable(nid) SELECT balanceid FROM t_paylist WHERE paytype=@paytype and userid=@userid AND balancetime BETWEEN @FromTime AND DATEADD(DAY,1,CONVERT(DATETIME,@ToTime)) order by balanceid desc
end
end
select O.balanceid,userid,username=(SELECT username FROM t_user WHERE userid=o.userid),O.balancetime,O.amount,o.samount,o.paytime,o.paystate,o.re ,o.webamount,o.counttime from t_paylist O,@indextable t where O.balanceid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
end
set nocount off
RETURN
GO
begin
if @sql3 is not null and @sql3 <> ''
的条件设定才行吧
不晓得你传来的值最初是什么样的
begin
if @sql3 is not null and @sql3 != ''
begin
set @strSql = @strSql +' '+'and indate between'''+cast(@sql2 as NVarChar)+'''' + 'and'''+cast(@sql3 as NVarChar)+''''
end
else if @sql2 is not null and @sql2 != ''
begin
set @strSql = @strSql +' '+'and indate='''+cast(@sql2 as NVarChar)+''''
end
else
begin
set @strSql = @strSql +''
end
end
这么写也是同样的问题
CREATE proc page
@pageIndex int, --页数
@pageSize int, --页面显示的数据量
@sql5 NVarChar (500)
as
declare @strSql varchar(5000) --查询的Sql语句if @pageIndex = '1'
set @strSql='select top' + str(@pageSize) + ' ' + 'pono,p_id from pur where p_id!='''''
else
begin
set @strSql = 'select top' + str(@pageSize) + ' ' + 'pono,p_id from pur'
set @strSql = @strSql + ' ' + 'where id <('
set @strSql = @strSql + 'select min(id) from ('
set @strSql = @strSql + 'select top '+ str((@pageIndex-1)*@pageSize) + +' id from pur order by id desc) as t' +') '
end if @sql5 is null or @sql5=''
set @strSql = @strSql + ''
else
begin
set @strSql = @strSql + ' ' + 'and depno='''+cast(@sql5 as NVarChar)+''''
end
exec(@strSql)
GO
@pageIndex int, --页数
@pageSize int, --页面显示的数据量
@sql1 NVarChar (500),
@sql2 NVarChar (500),
@sql3 NVarChar (500),
@strSql4 NVarChar (500)=''
as
declare @strSql varchar(5000) --查询的Sql语句if @sql1 is null or @sql1='' --条件一
set @strSql4 = @strSql4 + ''
else
if @sql1=7
begin
set @strSql4 = @strSql4 +' '+ 'and status <> 5'
end
else
begin
set @strSql4 = @strSql4+' '+ 'and status='+str(@sql1)
endif @sql2 is not null and @sql2 <> '' --条件二
begin
if @sql3 is not null and @sql3 <> ''
begin
set @strSql4 = @strSql4 +' '+'and indate between'''+cast(@sql2 as NVarChar)+'''' + 'and'''+cast(@sql3 as NVarChar)+''''
end
else
begin
set @strSql4 = @strSql4 +' '+'and indate='''+cast(@sql2 as NVarChar)+''''
end
endif @pageIndex = '1'
set @strSql='select top' + str(@pageSize) + ' ' + '* from pur where id!='''''+@strSql4
else
begin
set @strSql = 'select top' + str(@pageSize) + + '* from pur'
set @strSql = @strSql + ' ' + 'where id!='''''+@strSql4+' '+'and id <('
set @strSql = @strSql + 'select min(id) from ('
set @strSql = @strSql + 'select top '+ str((@pageIndex-1)*@pageSize) + +' id from pur where id!='''''+@strSql4+' '+' order by id desc) as t' +') '
end
set @strSql = @strSql +' '+'order by indate desc,pono desc'
--print(@strSql)
exec(@strSql)
GO