一个分页的问题~
CREATE PROCEDURE dbo.p_viKtor_B_AngelPicListByUserID
@UserID int,
@PageIndex int,
@PageNum int
AS
BEGIN
declare @TopNum int
declare @sql varchar(800)
declare @selectsql varchar(200)
set @TopNum = (@PageIndex -1) * @PageNum
set @sql = ' SELECT
Top ' +str(@PageNum) + 'AngelPicID,
UserID,
isnull(AlbumID,0) as AlbumID,
isnull(PicTitle, ' ') as PicTitle,
isnull(PicTrueSize, ' ') as PicTrueSize,
isnull(PicTypeID,0) as PicTypeID,
isnull(PicHit,0) as PicHit,
isnull(PicHeight,0) as PicHeight,
isnull(PicWidth,0) as PicWidth,
isnull(PicDescription, ' ') as PicDescription,
isnull(PicTrueAddr, ' ') as PicTrueAddr,
isnull(PicDisAddr, ' ') as PicDisAddr,
isnull(DeleteStatus,1) as DeleteStatus
FROM B_AngelPic
WHERE(AngelPicID not in (select top '+str(@TopNum)+ ' angelpicid from b_angelpic order by AngelPicID desc) and UserID = 105 and deleteStatus = 1)order by angelpicID desc '
exec(@sql)
end
再上面这个分页里面~可以通过mssql的编译~
但是调用得时候，提示：
字符串 ') as PicDisAddr,
isnull(DeleteStatus,1) as DeleteStatus
FROM B_AngelPic
WHERE(AngelPicID not in (select top 0 angelpicid from b_angelpic order by AngelPicID desc) and UserID = 105 and deleteStatus = 1)order by angelpicID desc ' 之前有未闭合的引号。
第 13 行: ') as PicDisAddr,
isnull(DeleteStatus,1) as DeleteStatus
FROM B_AngelPic
WHERE(AngelPicID not in (sel ' 附近有语法错误。
p_viKtor_B_AngelPicLoad: Cannot load data on p_viKtor_B_AngelPicLoad
///////////////////////////////////////////////
我后来调试了下，确定了就是isnull(PicDisAddr, ' ') as PicDisAddr,
这样的问题~但是数据允许为空~所以isnull又不能去掉~~
麻烦高手指教下~
[解决办法]
-- 把所有的 isnull(col, ' ') 改为isnull(col, ' ' ' ')
CREATE PROCEDURE dbo.p_viKtor_B_AngelPicListByUserID
@UserID int,
@PageIndex int,
@PageNum int
AS
BEGIN
declare @TopNum int
declare @sql varchar(800)
declare @selectsql varchar(200)
set @TopNum = (@PageIndex -1) * @PageNum
set @sql = ' SELECT Top ' +str(@PageNum) + 'AngelPicID,
UserID,
isnull(AlbumID,0) as AlbumID,
isnull(PicTitle, ' ' ' ') as PicTitle,
isnull(PicTrueSize, ' ' ' ') as PicTrueSize,
isnull(PicTypeID,0) as PicTypeID,
isnull(PicHit,0) as PicHit,
isnull(PicHeight,0) as PicHeight,
isnull(PicWidth,0) as PicWidth,
isnull(PicDescription, ' ' ' ') as PicDescription,
isnull(PicTrueAddr, ' ' ' ') as PicTrueAddr,
isnull(PicDisAddr, ' ' ' ') as PicDisAddr,
isnull(DeleteStatus,1) as DeleteStatus
FROM B_AngelPic
WHERE(AngelPicID not in (select top '+str(@TopNum)+ '
angelpicid from b_angelpic order by AngelPicID desc) and UserID = 105 and deleteStatus = 1)order by angelpicID desc '
exec(@sql)
end
