这个存储过程快把我搞疯了

[lzq0207]

使用这个存储过程能读出数据
CREATE PROCEDURE sp_selPage @count int,@page int,@tablename char(20),@fieldname char(20)
as DECLARE @SQL varchar(800)
SET @SQL='select top '+Cast(@count as char) +' * from '+@tablename+' where '+@fieldname+' not in
(select top '+CAST(@count*(@page-1) AS CHAR)+' '+@fieldname+' from '+@tablename+' order by '+@fieldname+' desc)
order by '+@fieldname+' desc'
EXEC(@SQL)
GO

可我加了个条件后怎么就读不出来了呢？如下：
CREATE PROCEDURE sp_selPage_a @count int,@page int,@tablename char(20),@fieldname char(20),@classID bigint
as DECLARE @SQL varchar(800)
SET @SQL='select top '+Cast(@count as char) +' * from '+@tablename+' where classname='+@classID+' and '+@fieldname+' not in
(select top '+CAST(@count*(@page-1) AS CHAR)+' '+@fieldname+' from '+@tablename
+' where classname='+@classID+' order by '+@fieldname+' desc)
order by '+@fieldname+' desc'
EXEC(@SQL)
GO

[lzq0207]

加了个@classID

[xluzhong]

---首先,把具体的数值代入变量,在查询分析器执行,看看结果.
---可能是@classID是bigint型,改为cast(@classID as nvarchar(10))
CREATE PROCEDURE sp_selPage_a @count int,@page int,@tablename char(20),@fieldname char(20),@classID bigint
as DECLARE @SQL varchar(800)
SET @SQL='select top '+Cast(@count as char) +' * from '+@tablename+' where classname='+@classID+' and '+@fieldname+' not in
(select top '+CAST(@count*(@page-1) AS CHAR)+' '+@fieldname+' from '+@tablename
+' where classname='+cast(@classID as nvarchar(10)+' order by '+@fieldname+' desc)
order by '+@fieldname+' desc'
EXEC(@SQL)
GO

[子陌红尘]

@classID ==> rtrim(@classID)
或者
@classID ==> cast(@classID as varchar(20))

[dxhdxh]

@classID ==> cast(@classID as nvarchar)

[jiang130]

將@classid設為varchar呢

[631799]

CREATE PROCEDURE sp_selPage_a @count int,@page int,@tablename char(20),@fieldname char(20),@classID varchar(50)
as DECLARE @SQL varchar(800)
SET @SQL='select top '+Cast(@count as char) +' * from '+@tablename+' where rtrim(classname)='''+rtrim(@classID)+''' and '+@fieldname+' not in
(select top '+CAST(@count*(@page-1) AS CHAR)+' '+@fieldname+' from '+@tablename
+' where rtrim(classname)='''+rtrim(@classID)+''' order by '+@fieldname+' desc)
order by '+@fieldname+' desc'
EXEC(@SQL)
GO