asp调用通用存储过程2007-03-07 17:09
create proc AbsolutePages
(
@PageSize int=10 , --每页显示的记录数
@PageCurrent int=1 , --当前要显示的页号
@FdName varchar(100)='' , --主键名或者标识列名
@SelectStr varchar(2000)='', --select子句,不包含select关键字,如:*或者id,UserId,UserName等。
@FromStr varchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable
@WhereStr varchar(2000)='', --where子句,不包含where关键字,如空的,或者 id>2 等
@OrderByStr varchar(1000)='' --order by 子句,不包含order by 子句 ,如id desc,UserId asc 等
)
as
------------------定义局部变量------------------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
declare @TmpStr varchar(2000) --临时语句
------------
if @WhereStr <> ''
set @WhereSqls = ' where (' + @WhereStr+ ')'
else
set @WhereSqls = ''
------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by '+ @OrderByStr
else
set @OrderBySqls = ''
------------
set @TmpStr = @WhereSqls
------------------如果显示第一页,可以直接用top来完成------------------
if @PageCurrent <= 1
begin
select @Id1 = convert(varchar(20),@PageSize)
set @Sqls = 'select top ' + @Id1 + ' ' + @SelectStr + ' from ' + @FromStr + @WhereSqls + @OrderBySqls
exec (@Sqls)
end
else
begin
select @Id1=convert(varchar(20),@PageSize),@Id2=convert(varchar(20),(@PageCurrent-1)*@PageSize)
if @WhereSqls <> ''
set @WhereSqls = @WhereSqls + ' and (' + @FdName +' not in(select top ' +@Id2+ ' '+ @FdName +' from ' +@FromStr+ @WhereSqls +@OrderBySqls+ '))'
else
set @WhereSqls = ' where ' + @FdName+ ' not in(select top '+ @Id2+ ' ' +@FdName+ ' from '+ @FromStr+ @WhereSqls+ @OrderBySqls+ ')'
----------
set @Sqls = 'select top '+ @Id1 + ' ' + @SelectStr+ ' from '+ @FromStr+@WhereSqls +@OrderBySqls
exec (@Sqls)
end
print @Sqls
return
GO
asp 调用:
Recordset1.Source = "{call dbo.AbsolutePages(" + "10,"+ Recordset1__pageid +",'product_id','product_id,product_name,class_id','product','class_id=1','product_id desc'" + ")}"
AbsolutePages '10','3','product_id','product_id,product_name,class_id','product','class_id=1','product_id desc'