sqlserver2000 分页存储过程 出错问题

[vip__888]

create procedure pagination
(
@startIndex int,
@endIndex int,
@docount bit,
@tableName varchar(50),
@order varchar(50),
@where varchar(1000)
)
as
set nocount on
if(@docount=1)
select count(*) from "+@tableName+"
else
begin
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select * from "+@tableName+" order by "+@order+"
select * from "+@tableName+" o,@indextable t where "+@where+"
and t.id between @startIndex and @endIndex order by t.id
end





以上是我用分页控件生成的存储过程，但是我改过之后，就出现问题了，不知道为什么出错，哪位大虾帮我看下！
set nocount off

[bancxc]

原来是什么样子的啊

[bancxc]

最起码的不能用双引号

[vip__888]

去掉双引号还是报错，

服务器: 消息 170，级别 15，状态 1，过程 pagination，行 13
第 13 行: '+' 附近有语法错误。
服务器: 消息 137，级别 15，状态 1，过程 pagination，行 18
必须声明变量 '@tableName'。
服务器: 消息 137，级别 15，状态 1，过程 pagination，行 19
必须声明变量 '@tableName'。

[zhujiazhi]

显然有问题的了
select count(*) from "+@tableName+"
这是麻意思的呢
declare @sql varchar(100)
set @sql = 'select count(*) from ' + @tableName + '
再执行这个sql的，

[vip__888]

楼上的可不可以给我一份完整的代码 我sql 都还给老师了

[vip__888]

帖子千万不要沉下去啊 顶起来

[fuda_1985]

create procedure pagination
(
@startIndex int,
@endIndex int,
@docount bit,
@tableName varchar(50),
@order varchar(50),
@where varchar(1000)
)
as
set nocount on
if(@docount=1)
select count(*) from "+@tableName+"
else
begin
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
declare @sql nvarchar(4000)
set @sql =
'insert into @indextable(nid) select * from '+@tableName+' order by '+@order+'
select * from '+@tableName+' o,@indextable t where '+@where+'
and t.id between @startIndex and @endIndex order by t.id '
exec(@sql)
end

[fuda_1985]

写错了。你本身的sql语句有问题!!!!!

[jdhlowforever]

这个存储过程我也很关注!

[aellonxie]

建议楼主这么写分页存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[Common_Page]
@page INT=1, --页数
@pagesize INT=20, --每页显示数据数
@fields nvarchar(800), --显示的字段名
@condition nvarchar(2000), --条件
@order nvarchar(500) --排序字段


--with encryption
AS
declare @SQLstr nvarchar(4000)
declare @opp nvarchar(500)
declare @endcount BIGINT
declare @maxcount INT
declare @endcoding INT
declare @endtime datetime

set nocount on

set @SQLstr='set @cc=(select count(1) ' + @condition + ')'
EXECUTE sp_executesql @SQLstr ,N'@cc INT output',@maxcount output
if @maxcount<=0
return (@maxcount)

if @page> (@maxcount-1)/@pagesize+1 set @page=(@maxcount-1)/@pagesize+1
set @endcount=@pagesize * (@page)

set @opp=replace(@order,' asc',' myasc')
set @opp=replace(@opp,' desc',' asc')
set @opp=replace(@opp,' myasc', ' desc')


if @page<=1
BEGIN
set @SQLstr ='select top ' + cast(@pagesize as nvarchar(15)) + ' ' + @fields + ' ' + @condition + ' order by ' + @order

END

else if @endcount > @maxcount /2
BEGIN
set @endcount=@maxcount-@endcount+@pagesize
set @SQLstr ='select top ' + cast(@pagesize as nvarchar(15)) +' * from (select top ' + cast(@endcount as nvarchar(15)) + ' ' + @fields + ' ' + @condition + ' order by ' + @opp +') as s1 order by ' + @order

END

else BEGIN
set @SQLstr ='select * from (select top ' + cast(@pagesize as nvarchar(15)) +' * from (select top ' + cast(@endcount as nvarchar(15)) + ' ' + @fields + ' ' + @condition + ' order by ' + @order +') as s1 order by ' + @opp +' ) as s2 order by ' + @order

END

--print @SQLstr
exec (@SQLstr)
return(@maxcount)



set nocount off
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

[vip__888]

谢谢楼上的几位