select top 后面不能跟变量吗?
需求:
实现一个简单的分页存储过程。不使用通用存储过程,不到万不得已不使用存储过程内的字符串拼接。
实现:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[sp_QueryPage_User]
@Page int = 1,
@PageSize int = 10
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select top @PageSize * from tbl_User
where (UserNativeId not in (select top @Page*@PageSize UserNativeId from tbl_User order by UserNativeId))
order by UserNativeId
END
go
错误提示:
Msg 102, Level 15, State 1, Procedure sp_QueryPage_User, Line 15
Incorrect syntax near '@PageSize'.
Msg 102, Level 15, State 1, Procedure sp_QueryPage_User, Line 16
Incorrect syntax near '@Page'.
分析: 将变量替换为常量后可以通过
例如将
select top @PageSize * from tbl_User
where (UserNativeId not in (select top @Page*@PageSize UserNativeId from tbl_User order by UserNativeId))
order by UserNativeId
替换为
select top 10 * from tbl_User
where (UserNativeId not in (select top 30 UserNativeId from tbl_User order by UserNativeId))
order by UserNativeId
求解: select top 后面不能跟变量?还是有什么别的语法可以通过的?