HomeBlogUtilizzo dei cursori in una stored procedure - SQL Server

Utilizzo dei cursori in una stored procedure - SQL Server

  1.  
  2. declare @id as int
  3. declare @codistat as varchar(10)
  4. declare @denominazione as varchar(100)
  5. declare @codistacapoluogo as varchar(100)
  6.  
  7. --The stored procedure will first declare a cursor:
  8. Declare @CrsrVar Cursor
  9.  
  10. -- The cursor will then be associated with the collection of properties related to the specified asset:
  11.  
  12. Set @CrsrVar = Cursor For
  13. Select id, codistat, denominazione, codistatcapoluogo From regione
  14.  
  15.  
  16. -- Before it can be used, the cursor needs to be opened:
  17.  
  18. Open @CrsrVar
  19.  
  20. -- The content of the first record can then be fetched into local variables:
  21.  
  22. Fetch Next From @CrsrVar
  23. Into @id,@codistat,@denominazione,@codistacapoluogo
  24.  
  25. While (@@FETCH_STATUS = 0)
  26. begin
  27. -- add your code ---------------------------
  28.  
  29. select @id
  30.  
  31. -- -----------------------------------------
  32.  
  33. -- After the values from the first record are processed, we read the next record:
  34.  
  35. Fetch Next From @CrsrVar Into @id,@codistat,@denominazione,@codistacapoluogo
  36.  
  37. end
  38. -- 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:
  39.  
  40. Close @CrsrVar
  41. Deallocate @CrsrVar
  42.  
Source code