sql 游标示例

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SelectSide](@career nchar(2))
AS
CREATE TABLE #tmp
(
 KeyWord nvarchar(50),
 Website1 nvarchar(50),
 URL1 varchar(100),
 Introduce1 nvarchar(max),
 Website2 nvarchar(50),
 URL2 varchar(100),
 Introduce2 nvarchar(max),
 Website3 nvarchar(50),
 URL3 varchar(100),
 Introduce3 nvarchar(max),
 Website4 nvarchar(50),
 URL4 varchar(100),
 Introduce4 nvarchar(max),
 Website5 nvarchar(50),
 URL5 varchar(100),
 Introduce5 nvarchar(max),
 Website6 nvarchar(50),
 URL6 varchar(100),
 Introduce6 nvarchar(max),
 Website7 nvarchar(50),
 URL7 varchar(100),
 Introduce7 nvarchar(max),
 Website8 nvarchar(50),
 URL8 varchar(100),
 Introduce8 nvarchar(max),
 Website9 nvarchar(50),
 URL9 varchar(100),
 Introduce9 nvarchar(max)
)
DECLARE @keyword nvarchar(max),
   @keyTmp nvarchar(50),
      @url varchar(100),
     @url1 varchar(100),
     @website nvarchar(50),
    @website1 nvarchar(50),
   @introduce nvarchar(max),
  @introduce1 nvarchar(max),
     @i int,
   @iBegin int,
     @iEnd int,
   @switch bit
  
SELECT     @keyword = KeyWord
FROM         TCareer
WHERE     (Career = @career)

SET @switch = 1

SET @iBegin = 1
SET @iEnd = CHARINDEX(',', @keyword, 1)

WHILE @iEnd > 0
BEGIN
 SET @keyTmp = SUBSTRING(@keyword, @iBegin, @iEnd - @iBegin)
 
 DECLARE cur1 CURSOR FOR
 SELECT     TOP (9) URL, Website, Introduce
 FROM         TAddress
 WHERE     (Career = @career) AND (KeyWord = @keyTmp)
 ORDER BY AddTime DESC

   OPEN cur1
   FETCH NEXT FROM cur1 INTO @url,@website,@introduce

   SET @i = 1
  
   WHILE @@FETCH_STATUS = 0
   BEGIN
   SET @introduce1 = REPLACE(@introduce,'''','''''')
   SET @url1 = REPLACE(@url,'''','''''')
   SET @website1 = REPLACE(@website,'''','''''')
      IF @i = 1
         INSERT INTO #tmp (KeyWord,Website1,URL1,Introduce1)
         VALUES (@keyTmp,@website,@url,@introduce)
      ELSE
         EXECUTE ('UPDATE #tmp SET URL'+ @i +'='''+ @url1 +''',Website'+ @i +' = N'''+ @website1 +''',Introduce'+ @i +' = N'''+ @introduce1 +'''
         WHERE (KeyWord = N'''+ @keyTmp +''')')
     
      SET @i = @i + 1
      FETCH NEXT FROM cur1 INTO  @url,@website,@introduce
   END

   CLOSE cur1
   DEALLOCATE cur1
  
   SET @iBegin = @iEnd + 1
   SET @iEnd = CHARINDEX(',',@keyword,@iBegin)
  
   IF @iEnd = 0 AND @switch = 1
   BEGIN
      SET @iEnd = LEN(@keyword) + 1
      SET @switch = 0
   END
END

SELECT * FROM #tmp

RETURN
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO