我在做分页存储,
请问我这个存储过程的判断哪有问题,主要是中间if语句,从页面传入空值时,搜索为空,去掉if语句就没问题
ALTER PROCEDURE dbo.GetAllbooking
(
@PageNumber int,
@ScorePerPage int,--每页显示的信息条数
@PageCount int output, --查询到的总页数
@SelectCount int output,--查询倒的数据总数
@bookid varchar(20),
@userid varchar(20)
)
AS
begin
create table #temp
(
rowid int,
bookid varchar(20)
)
SET NOCOUNT ON
declare @SelectCommand varchar(1000)
set @SelectCommand='insert into #temp
select row_number() over (order by booking.bkdate desc) as row,
booking.bookid from booking where (booking.bookid is not null)'
if (@bookid is not null)
set @SelectCommand=@SelectCommand+' and (booking.bookid='''+@bookid+''')'
if(@userid is not null)
begin
set @SelectCommand=@SelectCommand+' and (booking.userid='''+@userid+''')'
end
exec(@SelectCommand)
select @SelectCount=count(rowid) from #temp
set @pageCount=@SelectCount/@ScorePerPage
if (@PageCount<(@SelectCount*1.0/@ScorePerPage)) set @PageCount=@PageCount+1
select #temp.rowid,
booking.bookid, booking.userid, booking.bkdate, booking.productid, booking.toll,
booking.state,booking.class,booking.area
from booking inner join #temp on booking.bookid=#temp.bookid
where #temp.rowid>(@PageNumber-1)*@ScorePerPage and
#temp.rowid<=@PageNumber*@ScorePerPage
drop table #temp
end
请问我这个存储过程的判断哪有问题,主要是中间if语句,从页面传入空值时,搜索为空,去掉if语句就没问题
ALTER PROCEDURE dbo.GetAllbooking
(
@PageNumber int,
@ScorePerPage int,--每页显示的信息条数
@PageCount int output, --查询到的总页数
@SelectCount int output,--查询倒的数据总数
@bookid varchar(20),
@userid varchar(20)
)
AS
begin
create table #temp
(
rowid int,
bookid varchar(20)
)
SET NOCOUNT ON
declare @SelectCommand varchar(1000)
set @SelectCommand='insert into #temp
select row_number() over (order by booking.bkdate desc) as row,
booking.bookid from booking where (booking.bookid is not null)'
if (@bookid is not null)
set @SelectCommand=@SelectCommand+' and (booking.bookid='''+@bookid+''')'
if(@userid is not null)
begin
set @SelectCommand=@SelectCommand+' and (booking.userid='''+@userid+''')'
end
exec(@SelectCommand)
select @SelectCount=count(rowid) from #temp
set @pageCount=@SelectCount/@ScorePerPage
if (@PageCount<(@SelectCount*1.0/@ScorePerPage)) set @PageCount=@PageCount+1
select #temp.rowid,
booking.bookid, booking.userid, booking.bkdate, booking.productid, booking.toll,
booking.state,booking.class,booking.area
from booking inner join #temp on booking.bookid=#temp.bookid
where #temp.rowid>(@PageNumber-1)*@ScorePerPage and
#temp.rowid<=@PageNumber*@ScorePerPage
drop table #temp
end
解决方案 »
- 关于confirm的一个问题
- 偶尔出现 列XXX 不再table中 刷新一下就好了 怎么回事啊
- 关于showModalDialog页面中GridView内LinkButton的操作
- Replace()请看一下
- 远程服务器上,第二层文件夹,无法直接访问default.aspx
- 急!!!DATAGRID中有摸版列,要实现添加一行
- 【急求一好用的.NET混淆器】
- 关于Url重写和可删节的问题????
- 高手救命!为什么我使用SqlDataAdapter.Update()时告诉我影响了多少行,但是数据库里还是没有变化!
- 可用分过多,散一散~~~
- asp.net调用IE或者fireFox的收藏夹
- asp中的各种控件的用法谁有?
if (@bookid is not null and len(@bookid)>0) if(@userid is not null and len(@userid )>0)
@userid为空时查询所有数据
http://topic.csdn.net/u/20090814/15/f2402a09-f89d-4682-a08e-4025916e3f7f.html?86295