HomeBlogUtilizzo dei cursori in una stored procedure - SQL Server

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]

Save
Cookies user prefences
We use cookies to ensure you to get the best experience on our website. If you decline the use of cookies, this website may not function as expected.
Accetta
Continua senza accettare
Read more
Analytics
Tools used to analyze the data to measure the effectiveness of a website and to understand how it works.
Google Analytics
Accept
Decline