CREATE PROCEDURE ProductPage
(
@PageSize int, --传递过来的每页显示多少条数据
@CurrPage int --传递过来的当前面码
)
AS
--取得当前数据库的记录总数
declare @TotalputNum int
begin
select @TotalputNum=count(*) from Products--判断当前页数
if @TotalputNum>@PageSize --大于页面显示记录数,则分页
begin
select top @PageSize from Products where productid not in (select (@CurrPage-1)*@PageSize Productid from Products) order by productid desc
end
else
begin
select * from products order by productid desc
end
end
(
@PageSize int, --传递过来的每页显示多少条数据
@CurrPage int --传递过来的当前面码
)
AS
--取得当前数据库的记录总数
declare @TotalputNum int
begin
select @TotalputNum=count(*) from Products--判断当前页数
if @TotalputNum>@PageSize --大于页面显示记录数,则分页
begin
select top @PageSize from Products where productid not in (select (@CurrPage-1)*@PageSize Productid from Products) order by productid desc
end
else
begin
select * from products order by productid desc
end
end
begin
set rowcount @PageSize
select * from Products where productid not in (select (@CurrPage-1)*@PageSize Productid from Products) order by productid desc
set rowcount 0
end
else
begin
select * from products order by productid desc
end
end
declear @sqlstr nvarchar(300)
set @sqlstr='select top ''+str(@PageSize)+'' from Products where productid not in (select ''+str((@CurrPage-1)*@PageSize)+'' Productid from Products) order by productid desc 'exec (@sqlstr)
end
declear @sqlstr nvarchar(300)
set @sqlstr='select top ''+str(@PageSize)+'' from Products where productid not in (select ''+str((@CurrPage-1)*@PageSize)+'' Productid from Products) order by productid desc 'exec (@sqlstr)
end这个还是不行,会报错的,至于二楼的那个,你已经更改了我本来语句的用途
declare @sqlstr nvarchar(300)
set @sqlstr='select top ''+str(@PageSize)+'' from Products where productid not in (select ''+str((@CurrPage-1)*@PageSize)+'' Productid from Products) order by productid desc 'exec (@sqlstr)end这个可以啊....不过楼上的把声明参数declare写成了declear而已,当存储过程结构没有问题
declare @sqlstr nvarchar(300)
set @sqlstr='select top ''+str(@PageSize)+'' from Products where productid not in (select ''+str((@CurrPage-1)*@PageSize)+'' Productid from Products) order by productid desc 'exec (@sqlstr)end这个可以啊....不过楼上的把声明参数declare写成了declear而已,当存储过程结构没有问题
分析器分析是没问题,但执行的时候就会报告错误,不信你试试
declare @sqlstr nvarchar(300)
set @sqlstr='select top '+convert(varchar(10),@PageSize)+' from Products where productid not in (select '+convert(varchar(10),(@CurrPage-1)*@PageSize)+' Productid from Products) order by productid desc '
print @sqlstr --查看一下 sql语句是否有语法错误
exec (@sqlstr)end
set @sqlstr='select top ''+str(@PageSize)+'' from Products where productid not in (select ''+str((@CurrPage-1)*@PageSize)+'' Productid from Products) order by productid desc 'exec (@sqlstr)用exec方法说可以了,你先把SQL写好