帶遊標的預存程序

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[updatetushufenlei_beifabook]

AS
begin
declare @id int
declare @productname nvarchar(500)
declare @SearchKeyword varchar(1000)
declare @sql varchar(1000)
declare @zhi varchar(1000)
set @zhi=' 北發圖書網'+' 品牌::品牌 商家:100057:商家 分類:05:分類'

--申明一個遊標
DECLARE authors_cursor CURSOR FOR
SELECT   id, productname FROM gwkproduct where ident like 'beifabook_%' and (BrowseNodeKeyword ='' or BrowseNodeKeyword is  null)
--開啟遊標
OPEN authors_cursor
--取出值
FETCH NEXT FROM authors_cursor INTO @id, @productname
--迴圈取出遊標的值
WHILE @@FETCH_STATUS = 0
BEGIN
Print @id
Print @productname
Print '‘ ’ '
update gwkproduct set bdel='1', SearchKeyword='05',BrowseNodeKeyword=@productname +@zhi where id=@id
FETCH NEXT FROM authors_cursor
INTO @id, @productname
END
CLOSE authors_cursor --關閉遊標
DEALLOCATE authors_cursor --釋放遊標

--execute sp_executesql
--sp_executesql(@sql)
end;