大家帮帮忙看一下这个存储过程老是报错。
ALTER PROCEDURE sp_gb_select_for_pages
@startpage INT=1,
@pagesize INT=10,
@pagecount INT OUTPUT
AS
SET NOCOUNT ON
IF(@startpage> 0)
SET @startpage=(@startpage-1)*@pagesize
SELECT @pagecount=count(*)FROM bookinfo
EXEC( 'SELECT TOP '+ @pagesize+ ' *FROM bookinfo
WHERE id not in
( SELECT TOP ' +@startpage+ 'id FROM bookinfo ORDER BY id DESC)
ORDER BY id DESC ')
RETURN


报错：
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM '.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ORDER '.
谢谢


[解决办法]
要用动态sql
ALTER PROCEDURE sp_gb_select_for_pages
@startpage INT=1,
@pagesize INT=10,
@pagecount INT OUTPUT
AS
SET NOCOUNT ON
IF(@startpage> 0)
SET @startpage=(@startpage-1)*@pagesize
SELECT @pagecount=count(*) FROM bookinfo
EXEC( 'SELECT TOP '+ @pagesize+ ' * FROM bookinfo WHERE id not in
( SELECT TOP ' +@startpage+ ' id FROM bookinfo ORDER BY id DESC)
ORDER BY id DESC ')
RETURN


注意空格

[解决办法]
create table bookinfo(id int
)
insert into bookinfo select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
union select 11
union select 12
union select 13
union select 14
union select 15
go
create PROCEDURE sp_gb_select_for_pages
@startpage INT=1,
@pagesize INT=10,
@pagecount INT OUTPUT
AS
SET NOCOUNT ON
declare @sql nvarchar(200)
IF(@startpage> 0)
SET @startpage=(@startpage-1)*@pagesize
SELECT @pagecount=count(*) FROM bookinfo
set @sql= 'SELECT TOP '+ Convert(varchar(20),@pagesize)+ ' * FROM bookinfo
WHERE id not in
( SELECT TOP ' +Convert(varchar(20),@startpage)+ ' id FROM bookinfo ORDER BY id DESC)
ORDER BY id DESC '
exec (@sql)
RETURN
go
----

DECLARE@pagecount int

EXEC[dbo].[sp_gb_select_for_pages]
@pagecount = @pagecount OUTPUT

SELECT@pagecount as N '@pagecount '


GO

-----
drop table bookinfo
drop procedure sp_gb_select_for_pages
go
----
id
15
14
13
12
11
10
9
8
7
6
----
@pagecount
15