如题,
存储过程如下:CREATE PROCEDURE userinfo_page_list
@Page int = 1,
@PageSize int = 10
AS
declare @sql varchar(max)
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @sql='select top'+ @PageSize + ' * from userinfo
where' + ' ('+'userid not in'+' (select top '+@Page*(@PageSize-1)+' userid from userinfo order by userid'+'))'+
'order by userid'
exec(@sql)
print @sql
END
go
调用:
exec userinfo_page_list 1,2报错:
消息 245,级别 16,状态 1,过程 userinfo_page_list,第 11 行
Conversion failed when converting the varchar value 'select top' to data type int.SQL Server分页SQL存储
存储过程如下:CREATE PROCEDURE userinfo_page_list
@Page int = 1,
@PageSize int = 10
AS
declare @sql varchar(max)
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @sql='select top'+ @PageSize + ' * from userinfo
where' + ' ('+'userid not in'+' (select top '+@Page*(@PageSize-1)+' userid from userinfo order by userid'+'))'+
'order by userid'
exec(@sql)
print @sql
END
go
调用:
exec userinfo_page_list 1,2报错:
消息 245,级别 16,状态 1,过程 userinfo_page_list,第 11 行
Conversion failed when converting the varchar value 'select top' to data type int.SQL Server分页SQL存储
(後面加一個空格)
CREATE PROCEDURE userinfo_page_list
@Page int = 1,
@PageSize int = 10
AS
declare @sql varchar(max)
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @sql='select top '+ rtrim(@PageSize) + ' * from userinfo
where' + ' ('+'userid not in'+' (select top '+rtrim(@Page*(@PageSize-1))+' userid from userinfo order by userid'+'))'+
'order by userid'
exec(@sql) END
go
再 exec(@sql)
你看下 print 的语句到底是什么,就知道自己错在哪里了
糾正為:
set @sql='select top '+ cast(@PageSize as varchar)+...
@Page int = 1,
@PageSize int = 10
AS
declare @sql varchar(max)
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @sql='select top '+ @PageSize + ' * from userinfo --top 后面加一空格
where' + ' ('+'userid not in'+' (select top '+@Page*(@PageSize-1)+' userid from userinfo order by userid'+'))'+
'order by userid'
exec(@sql)
print @sql
END
go
@Page int = 1,
@PageSize int = 10
AS
declare @sql varchar(max)
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--关键问题在pagesize是int 是不能用+连接的,所以要加上cast或convert函数转换成字符型就OK
set @sql='select top'+ CAST(@PageSize AS VARCHAR(10))+ ' * from userinfo
where' + ' ('+'userid not in'+' (select top '+cast(@Page*(@PageSize-1) as varchar)+' userid from userinfo order by userid'+'))'+
'order by userid'
exec(@sql)
print @sql
END
go
@Page*(@PageSize-1)
Top 后面 空格 ,而且 INt 必须转换为varchar
@PageSize int = 10
declare @sql varchar(max)
--set @sql='select top'+ @PageSize + ' * from userinfo
set @sql='select top'+ convert(nvarchar(20),@PageSize) + ' * from userinfo
where' + ' ('+'userid not in'+' (select top '+convert(nvarchar(20),@Page*(@PageSize-1))+' userid from userinfo order by userid'+'))'+
'order by userid'
int 和 varchar 拼接的时候须把 int 做类型转换