您的位置：程序门 -> ms-sql server -> 应用实例

[求救]存储过程问题，回复重奖

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

[求救]存储过程问题，回复重奖

发表于：2007-03-22 10:19:41 楼主

分页存储过程：

create procedure [getcustomersdatapage]

@pageindex int,

@pagesize int,

@recordcount int out,

@pagecount int out,

@khlx int

as

select @recordcount = count（*) from customer

set @pagecount = ceiling（@recordcount * 1.0 / @pagesize)

declare @sqlstr nvarchar（1000)

if @pageindex = 0 or @pagecount <= 1

set @sqlstr =n 'select top '+str（ @pagesize )+

' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id=@khlx order by customer.customer_id desc '

else if @pageindex = @pagecount - 1

set @sqlstr =n ' select * from （ select top '+str（ @recordcount - @pagesize * @pageindex )+

' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id=@khlx order by customer.customer_id asc ) temptable order by customer.customer_id desc '

else

set @sqlstr =n ' select top '+str（ @pagesize )+ ' * from （ select top '+str（ @recordcount - @pagesize * @pageindex )+

' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id=@khlx order by customer.customer_id asc ) temptable order by ccustomer.customer_id desc '

ＥＸＥＣ（@sqlstr)

go

调用存储过程的代码：

private static dataset getcustomersdata（int pageindex,int pagesize,ref int recordcount,ref int pagecount,int khlx)

{
sqlconnection conn=db.createconnection（);
sqlcommand comm = new sqlcommand（ "getcustomersdatapage ",conn);
comm.parameters.add（new sqlparameter（ "@pageindex ",sqldbtype.int));
comm.parameters[0].value = pageindex;
comm.parameters.add（new sqlparameter（ "@pagesize ",sqldbtype.int));
comm.parameters[1].value = pagesize;
comm.parameters.add（new sqlparameter（ "@recordcount ",sqldbtype.int));
comm.parameters[2].direction = parameterdirection.output;
comm.parameters.add（new sqlparameter（ "@pagecount ",sqldbtype.int));
comm.parameters[3].direction = parameterdirection.output;
comm.parameters.add（new sqlparameter（ "@khlx ",sqldbtype.int));
comm.parameters[4].value = khlx;
comm.commandtype = commandtype.storedprocedure;
sqldataadapter dataadapter = new sqldataadapter（comm);
dataset ds = new dataset（);
dataadapter.fill（ds);
recordcount = （int)comm.parameters[2].value;
pagecount = （int)comm.parameters[3].value;
return ds;
}

运行出错提示为：必须声明变量 '@khlx '。请各位大虾帮忙指正。急急

发表于：2007-03-22 10:22:381楼得分:0

奖呢？呵呵。

发表于：2007-03-22 10:24:032楼得分:0

try

create procedure [getcustomersdatapage]

@pageindex int,

@pagesize int,

@recordcount int out,

@pagecount int out,

@khlx int

as

select @recordcount = count（*) from customer

set @pagecount = ceiling（@recordcount * 1.0 / @pagesize)

declare @sqlstr nvarchar（1000)

if @pageindex = 0 or @pagecount <= 1

set @sqlstr =n 'select top '+str（ @pagesize )+

' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id= ' + rtrim（@khlx) + ' order by customer.customer_id desc '

else if @pageindex = @pagecount - 1

set @sqlstr =n ' select * from （ select top '+str（ @recordcount - @pagesize * @pageindex )+

' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id= ' + rtrim（@khlx) + ' order by customer.customer_id asc ) temptable order by customer.customer_id desc '

else

set @sqlstr =n ' select top '+str（ @pagesize )+ ' * from （ select top '+str（ @recordcount - @pagesize * @pageindex )+

' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id= ' + rtrim（@khlx) + ' order by customer.customer_id asc ) temptable order by ccustomer.customer_id desc '

ＥＸＥＣ（@sqlstr)

go

发表于：2007-03-22 10:25:283楼得分:0

錯誤在這裡

and csort.csort_id=@khlx order by customer.customer_id desc '

改為

and csort.csort_id= ' + rtrim（@khlx) + ' order by customer.customer_id desc '

其余幾處用到@khlx的地方也做類似修改。

发表于：2007-03-22 11:34:354楼得分:0

请问楼上的是不是在广东惠州读书的啊

发表于：2007-03-22 11:36:095楼得分:0

特为领奖而来~~~

发表于：2007-03-22 11:50:576楼得分:0

獎勵汽車一輛----------------------------------玩具牌

发表于：2007-03-22 12:29:247楼得分:0

字符串组起来的sql语句里的变量写错了

set @sqlstr =n ' select * from （ select top '+str（ @recordcount - @pagesize * @pageindex )+ ' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id=@khlx order by customer.customer_id asc ) temptable order by customer.customer_id desc '
修改成
set @sqlstr =n ' select * from （ select top '+str（ @recordcount - @pagesize * @pageindex )+ ' * from customer ,csort where customer.csort_id=csort.csort_id and csort.csort_id= '+convert（varchar,@khlx) + ' order by customer.customer_id asc ) temptable order by customer.customer_id desc '
就可以了

else下面那部分也一样

发表于：2007-03-22 13:48:418楼得分:0

特来关注一下，用print （@sqlstr)看看结果，再将结果语法检查一下，应该可以找到是哪一行问题了