100 分求救 一个大家很熟悉的存储过程的问题！！！

[dfesd]

我用的是 ASP.NET结合存储过程写的通用搜索分页程序 网上找的 谁写的就不知道了 但是发现存在缺陷 WHERE 语句如果有 or 例如 ClassId=1 or ClassId=2 就不好用了

存储过程代码如下：
--------------------------------------------------------------------------------


up_GetTopicList.sql

--------------------------------------------------------------------------------

CREATE proc up_GetTopicList
@a_TableList Varchar(200),
@a_TableName Varchar(30),
@a_SelectWhere Varchar(500),
@a_SelectOrderId Varchar(20),
@a_SelectOrder Varchar(50),
@a_intPageNo int,
@a_intPageSize int,
@RecordCount int OUTPUT
as
/*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar(600)
/*关闭计数*/
set nocount on

/*求总共根贴数*/

select @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@a_TableName+' '+@a_SelectWhere
execute sp_executesql
@TmpSelect,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUT

select @RecordCount = @intRootRecordCount

if (@intRootRecordCount = 0) --如果没有贴子，则返回零
return 0

/*判断页数是否正确*/
if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
return (-1)

/*求开始rootID*/
set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
/*限制条数*/

select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintBeginID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT


/*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/*限制条数*/

select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintEndID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintEndID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintEndID=@intEndID OUTPUT


if @a_SelectWhere='' or @a_SelectWhere IS NULL
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' where '+@a_SelectOrderId+' between '
else
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' '+@a_SelectWhere+' and '+@a_SelectOrderId+' between '

if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect+'@SPintBeginID and @SPintEndID'+' '+@a_SelectOrder
else
select @TmpSelect = @TmpSelect+'@SPintEndID and @SPintBeginID'+' '+@a_SelectOrder

execute sp_executesql
@TmpSelect,
N'@SPintEndID int,@SPintBeginID int',
@SPintEndID=@intEndID,@SPintBeginID=@intBeginID

return(@@rowcount)
--select @@rowcount
GO

[dfesd]

是 存储过程 的毛病 如果WHERE 语句包含了 OR 就并不能正常分页了 就把所有的数据都给读出来了 用 and 就没有关系
哪位高手帮忙看看这个存储过程啊 谢谢

[zuoxxx]

都mark 我也m一下看看
帮顶

[dfesd]

应用例子

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

protected void Page_Load(Object sender, EventArgs e)
{
int intPageNo,intPageSize,intPageCount;
intPageSize = 25;
if (Request["CurrentPage"]==null)
{
intPageNo = 1;
}
else
{
intPageNo = Int32.Parse(Request["CurrentPage"]);
}


SqlConnection mySqlConnection = new SqlConnection("server=(local);Database=test;user id=sa;password=");
SqlCommand mySqlCommand = new SqlCommand("up_GetTopicList", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;

SqlParameter workParm;

//搜索表字段，以","号分隔
workParm = mySqlCommand.Parameters.Add("@a_TableList", SqlDbType.VarChar, 200);
mySqlCommand.Parameters["@a_TableList"].Value = "OFFERID,type,offertime";

//搜索表名
workParm = mySqlCommand.Parameters.Add("@a_TableName", SqlDbType.VarChar, 30);
mySqlCommand.Parameters["@a_TableName"].Value = "offer";

//搜索条件，如"select * from aa where a=1 and b=2 and c=3"则条件为"where a=1 and b=2 and c=3"
workParm = mySqlCommand.Parameters.Add("@a_SelectWhere", SqlDbType.VarChar, 500);
mySqlCommand.Parameters["@a_SelectWhere"].Value = "where type='idl'";

//表主键字段名，必须为INT类型
workParm = mySqlCommand.Parameters.Add("@a_SelectOrderId", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrderId"].Value = "offerid";

//排序，可以使用多字段排序但主键字段必需在最前面
workParm = mySqlCommand.Parameters.Add("@a_SelectOrder", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrder"].Value = "order by offerid desc";

//页号
workParm = mySqlCommand.Parameters.Add("@a_intPageNo", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageNo"].Value = intPageNo;

//每页显示数
workParm = mySqlCommand.Parameters.Add("@a_intPageSize", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageSize"].Value = intPageSize;

//总记录数(存储过程输出参数)
workParm = mySqlCommand.Parameters.Add("@RecordCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;

//当前页记录数(存储过程返回值)
workParm = mySqlCommand.Parameters.Add("RowCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.ReturnValue;

mySqlConnection.Open();
Repeater.DataSource = mySqlCommand.ExecuteReader();

Repeater.DataBind();

mySqlConnection.Close();

Int32 RecordCount = (Int32)mySqlCommand.Parameters["@RecordCount"].Value;
Int32 RowCount = (Int32)mySqlCommand.Parameters["RowCount"].Value;

LabelRecord.Text = RecordCount.ToString();
LabelRow.Text = intPageNo.ToString();
intPageCount = RecordCount/intPageSize;
if ((RecordCount%intPageSize)>0)
intPageCount += 1;
LabelPage.Text = intPageCount.ToString();

if (intPageNo>1)
{
HLFistPage.NavigateUrl = "select.aspx?CurrentPage=1";
HLPrevPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo-1);
}
else
{
HLFistPage.NavigateUrl = "";
HLPrevPage.NavigateUrl = "";
//HLFistPage.Enabled = false;
//HLPrevPage.Enabled = false;
}

if (intPageNo<intPageCount)
{
HLNextPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo+1);
HLEndPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageCount);
}
else
{
HLNextPage.NavigateUrl = "";
HLEndPage.NavigateUrl = "";
//HLNextPage.Enabled=false;
//HLEndPage.Enabled=false;
}

}

</script>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<head>
<link href="/style.css" rel="stylesheet" />
<style type="text/css">
.high { font-family: "宋体"; font-size: 9pt; line-height: 140%}
.mid { font-size: 9pt; line-height: 12pt}
.small { font-size: 9pt; line-height: normal}
.TP10_5 {
font-size: 14px;
line-height: 140%;
}
</style>
<style type="text/css">A:link {
COLOR: #cc6666
}
</style>
</head>
<body>
<form runat="server">
<span class="high"> 第<font color="#CC0000"><asp:Label id="LabelRow" runat="server"/></font>页 | 共有<asp:Label id="LabelPage" runat="server"/>页
| <asp:Label id="LabelRecord" runat="server"/>条信息 |
<asp:HyperLink id="HLFistPage" Text="首页" runat="server"/>
| <asp:HyperLink id="HLPrevPage" Text="上一页" runat="server"/>
| <asp:HyperLink id="HLNextPage" Text="下一页" runat="server"/>
| <asp:HyperLink id="HLEndPage" Text="尾页" runat="server"/></span><br>

<asp:Repeater id=Repeater runat="server">

<HeaderTemplate>

<table width="583" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#000000"><table width="100%" border="0" cellpadding="4" cellspacing="1" class="TP10_5">
<tr bgcolor="#999999">
<td align="center"> <strong><font color="#FFFFFF">订单号</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">服务项目</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">预订日期</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">操作人员</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">分配状态</font></strong></td>
<td> <div align="center"></div></td>
</tr>
</HeaderTemplate>

<ItemTemplate>

<tr align="center" bgcolor="#FFFFFF" class="small" onMouseOver='this.style.background="#CCCCCC"' onMouseOut='this.style.background="#FFFFFF"'>
<td><%# DataBinder.Eval(Container.DataItem, "offerid") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "type") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "offertime") %></td>
<td> </td>
<td> </td>
<td><a href="javascript:void(window.open('info.asp?id=<%# DataBinder.Eval(Container.DataItem, "offerid") %>','订单分配','height=600,width=1000'))">订单详情</a></td>
</tr>

</ItemTemplate>

<FooterTemplate>

</table></td>
</tr>
</table>

</FooterTemplate>

</asp:Repeater>

</form>
</body>
</html>

[amandag]

mark

[liujiayu10]

每天都能看到楼上那位仁兄在 mark，不知道他顶出个星星有什么用！

[dfesd]

怎么没有人帮忙啊

[ilovejolly]

mark

[sbsrg3000]

mark有错吗?不就是想多搞点分,好遇到不能解决的问题再问上啊,没分提问没人帮忙解决就好比没钱医院都不让你看病的样子!帮LZ把问题顶上去没错吧.我刚刚入行不到几天,我就是天天MARK居中多,谁叫我是菜鸟,我又不图小三解,只图有高手解出题目,好多学着点....MARK无罪!

[自然框架]

绑顶

[happyamei]

兄弟，你看看这个存储过程，据说不错

CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 排序字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(2000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(1000) -- 排序类型

if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder

if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where (' + @strWhere + ') '
+ @strOrder + ') as tblTmp) and (' + @strWhere + ') ' + @strOrder

if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end

if @IsCount != 0
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

exec (@strSQL)
GO

[新鲜鱼排]

“发现存在缺陷 WHERE 语句如果有 or 例如 ClassId=1 or ClassId=2 就不好用了”
我觉得可能还是存储过程的问题，楼主说的不好用是只出现错误,还是说数据查询的结果不正确哪??

[dfesd]

原来用 OR 的时候要用括号括起来

请问各位谁知道 这个存储过程中 N'@SPintEndID int,@SPintBeginID int',

这个 N' 和 @SP 是什么意思哦？

[winner2050]

基本是乱抄

[dfesd]

哦 原来 N' Unicode编码的意思 那 @SP+加变量名是怎么回事哦 找了半天也没有找到解释的

[yuanlfrj]

sp_executesql 支持与 Transact-SQL 字符串相独立的参数值的设置
拿你上面的代码举个例子:
select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
看上面的select语句,有两个独立的参数(就是没有定义的参数),@SPintRowCount,@SPintBeginID
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintBeginID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT
在execute sp_executesql中,我们才开始定义@SPintRowCount,@SPintBeginID
定义部分就是N'@SPintRowCount int,@SPintBeginID int OUTPUT',同时用我们已经定义的参数
@intRowCount,@intBeginID的值替换,@SPintRowCount,@SPintBeginID

[sbsrg3000]

乱马1/2一样

[lizhengqin]

可以自己写一个.