CREATE PROCEDURE Xl_Article_pro_getprenext
@articleId int
AS
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid , max(title) title from Xl_Article where articleid <@articleId
group by title
union all
select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid ,max(title) title from Xl_Article where articleid>@articleId
group by titl
@articleId int
AS
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid , max(title) title from Xl_Article where articleid <@articleId
group by title
union all
select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid ,max(title) title from Xl_Article where articleid>@articleId
group by titl
13> select * from Xl_Article;
14> go(1 rows affected)
articleid |title
-----------|----------
7|A01
9|A02
10|A03
14|A04
21|A05
29|A06
31|A07
36|A08
41|A09
43|A10
44|A11
49|A12(12 rows affected)
1> exec Xl_Article_pro_getprenext(10)
2> go
Msg 102, Level 15, State 1, Server WUXN0058\SQLEXPRESS, Line 1
Incorrect syntax near '10'.
1> exec Xl_Article_pro_getprenext 10
2> go
articleid |title
-----------|----------
9|A02
14|A04(2 rows affected)
1>CREATE PROCEDURE Xl_Article_pro_getprenext
@articleId int
AS
declare @a1 int
declare @a2 int
select @a1=isnull(max(articleid),(select max(articleid) from Xl_Article)) from Xl_Article where articleid <@articleId
select @a2=isnull(min(articleid),(select max(articleid) from Xl_Article)) from Xl_Article where articleid >@articleId
select articleid ,title from Xl_Article where articleid=@a1 or articleid=@a2
go
group by title-- Author : HappyFlyStone
-- Date : 2010-01-01
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('Xl_Article') IS NOT NULL
DROP TABLE Xl_Article
Go
CREATE TABLE Xl_Article([articleid] INT,[title] NVARCHAR(3))
Go
INSERT INTO Xl_Article
SELECT 7,'A01' UNION ALL
SELECT 9,'A02' UNION ALL
SELECT 10,'A03' UNION ALL
SELECT 14,'A04' UNION ALL
SELECT 21,'A05' UNION ALL
SELECT 29,'A06' UNION ALL
SELECT 31,'A07' UNION ALL
SELECT 36,'A08' UNION ALL
SELECT 41,'A09' UNION ALL
SELECT 43,'A10' UNION ALL
SELECT 44,'A11' UNION ALL
SELECT 49,'A12'
GO
--Start
CREATE PROCEDURE Xl_Article_pro_getprenext
@articleId int
ASselect a.articleid ,a.title from Xl_Article a ,
(
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid from Xl_Article where articleid <@articleId
union
select isnull(min(articleid),(select max(articleid) from Xl_Article)) from Xl_Article where articleid >@articleId
) b
where a.articleid = b.articleid
goexec Xl_Article_pro_getprenext 10drop proc Xl_Article_pro_getprenext--Result:
/*
articleid title
----------- -----
9 A02
14 A04(2 行受影响)
*/
--End