Question

sql存储过程里top后面可以跟参数吗？

CREATE PROCEDURE dbo.procTopicListByBoardIdSelect
@boardId INT,
@rowBegin INT
AS
BEGIN
SELECT TOP 20 *
FROM dbo.TBL_TOPIC
WHERE boardId=@boardId AND topicId NOT IN ( SELECT TOP @rowBegin topicId
FROM dbo.TBL_TOPIC
WHERE boardId=@boardId
ORDER BY publishTime DESC)
ORDER BY publishTime DESC
END
GO

TOP @rowBegin 。。。。会报错，怎么解决？根据@rowBegin传过来的参数查询top数据。
那怎么在java里调用？

Answer 1

直接跟参数的形式，从 SQL 2005 才开始支持。 SQL 2000 是不支持的。

如果一定要使用的话，只能用 EXEC(@SQL)的方式执行

Answer 2

dbo.procTopicListByBoardIdSelect
@boardId INT,
@rowBegin INT
AS
BEGIN
declare @strtmp varchar(1000)
set @strtmp='SELECT TOP 20 *
FROM dbo.TBL_TOPIC
WHERE boardId=@boardId AND topicId NOT IN ( SELECT TOP '+ convert(varchar(100), @rowBegin) + ' topicId
FROM dbo.TBL_TOPIC
WHERE boardId=@boardId
ORDER BY publishTime DESC)
ORDER BY publishTime DESC '
execute @strtmp
END
GO

Answer 3

可以，要用括号括起来
TOP （@rowBegin）