有这样一个存储过程,是用来分页的
CREATE PROCEDURE mypage
@tb varchar(50),
--表名
@col varchar(50),
--按该列来进行分页
@coll varchar(50),
--排序的列
@coltype int,
--@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby int,
--排序,0-顺序,1-倒序
@collist varchar(800),
--要查询出的字段列表,*表示全部字段
@pagesize int,
--每页记录数
@page int,
--指定页
@condition varchar(800) ,
--查询条件
@pages int OUTPUT,
--总页数
@countnumeric int OUTPUT
--总记录数
ASDECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN
--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN
--有查询条件
SET @where1=' WHERE ('+@condition+') AND '
--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '
--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT SET @sql='SELECT @countnumeric=CEILING((COUNT(*)+0.0)) FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@countnumeric int OUTPUT',@countnumeric OUTPUT
--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@coll
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@coll+' DESC'
IF @page=1
--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@coll+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO现在我要实现的sql语句如下:
select x.wid,x.wname,x.wsmimgpath,sayword.sword,sayword.unicknme, sayword.stitle,x.sid
from
(select ware.wid wid,ware.wname wname,ware.wsmimgpath,min(sayword.sid) sid from ware,sayword where ware.wid=sayword.wid and sayword.bokeid<>0
group by ware.wid,ware.wname,ware.wsmimgpath) x, sayword
where x.sid = sayword.sid order by x.wid desc
那么,在用存储过程的时候,各个参数应该为多少?高手指点一下
@tb varchar(50),
--表名
@col varchar(50),
--按该列来进行分页
@coll varchar(50),
--排序的列
@coltype int,
--@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby int,
--排序,0-顺序,1-倒序
@collist varchar(800),
--要查询出的字段列表,*表示全部字段
@pagesize int,
--每页记录数
@page int,
--指定页
@condition varchar(800) ,
cmd.Parameters["@tb"].Value = "(select ware.wid wid,ware.wname wname,ware.wsmimgpath,min(sayword.sid) sid from ware,sayword where ware.wid=sayword.wid and sayword.bokeid<>0group by ware.wid,ware.wname,ware.wsmimgpath) x, sayword";
cmd.Parameters["@col"].Value = "x.wid";
cmd.Parameters["@coll"].Value = "x.wid";cmd.Parameters["@coltype"].Value = 0;cmd.Parameters["@orderby"].Value = 1;cmd.Parameters["@collist"].Value = "x.wid,x.wname,x.wsmimgpath,sayword.sword,sayword.unicknme, sayword.stitle";cmd.Parameters["@pagesize"].Value = 20;cmd.Parameters["@page"].Value = page;cmd.Parameters["@condition"].Value =" x.sid = sayword.sid ";大家帮我看看哪错了,谢谢
第 1 行: ')' 附近有语法错误。
除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
着是第一行???
http://blog.csdn.net/zhzuo/archive/2006/09/30/1313274.aspx
@tableName varchar(50),
@colName varchar(150),
@orderby int,
@colListForScan varchar(1000),--要查出的字段,*表示全部
@pageSize int,
@pageIndex int,
@condition varchar(1000),--查询条件
@pagesCount int output,
@rowsCount int output
as
declare @sql varchar(3000),@strOrder varchar(300),@strMid varchar(800)select @sql = 'select top '+ str(@pageSize)+ ' ' + @colListForScan
+ ' from '+ @tableNameselect @strMid = '('+ @colName +') from (select top '
+str((@pageIndex-1)*@pageSize)+' '+@colName+' from '+@tableNameif @condition is not null or len(@condition) > 0
begin
select @sql = @sql + ' where ('+@condition+') and '+ @colName
select @strMid = @strMid + ' where ('+@condition+') order by '
end
else
begin
select @sql = @sql + ' where '+ @colName
select @strMid = @strMid + ' order by '
end
--print @sql
--print @strMid
if @orderby = 0--顺序
begin
select @strOrder = ' > (select max '
select @sql = @sql + @strOrder + @strMid + @colName +' )t) order by '
+ @colName
end
else--逆序
begin
select @strOrder = ' < (select min '
select @sql = @sql + @strOrder + @strMid + @colName +' desc)t) order by '
+ @colName + ' desc'
end
--print @sql
exec(@sql)GO