Utilizzo dei cursori in una stored procedure - SQL Server

Vediamo come utilizzare i cursori di SQL Server in una stored procedure

[code start:0 lang:sql]
declare @id as int
declare @codistat as varchar(10)
declare @denominazione as varchar(100)
declare @codistacapoluogo as varchar(100)

--The stored procedure will first declare a cursor:
Declare @CrsrVar Cursor

-- The cursor will then be associated with the collection of properties related to the specified asset:

Set @CrsrVar = Cursor For
Select id, codistat, denominazione, codistatcapoluogo From regione


-- Before it can be used, the cursor needs to be opened:

Open @CrsrVar    

-- The content of the first record can then be fetched into local variables:

Fetch Next From @CrsrVar
Into @id,@codistat,@denominazione,@codistacapoluogo

While (@@FETCH_STATUS = 0)
begin
-- add your code ---------------------------

select @id

-- -----------------------------------------

-- After the values from the first record are processed, we read the next record:

Fetch Next From @CrsrVar Into @id,@codistat,@denominazione,@codistacapoluogo

end
-- Once all records have been read, the value of @@fetch_status is set to –1 and we exit the loop. We need to close and deallocate the cursor and finish the stored procedure:

Close @CrsrVar
Deallocate @CrsrVar
[/code]