sql cursor example

1
2        
3            
4        
5     DECLARE 
6    @product_name VARCHAR(MAX), 
7    @list_price   DECIMAL;
8
9DECLARE cursor_product CURSOR
10FOR SELECT 
11        product_name, 
12        list_price
13    FROM 
14        production.products;
15
16OPEN cursor_product;
17
18FETCH NEXT FROM cursor_product INTO 
19    @product_name, 
20    @list_price;
21
22WHILE @@FETCH_STATUS = 0
23    BEGIN
24        PRINT @product_name + CAST(@list_price AS varchar);
25        FETCH NEXT FROM cursor_product INTO 
26            @product_name, 
27            @list_price;
28    END;
29
30CLOSE cursor_product;
31
32DEALLOCATE cursor_product;
33

1DECLARE @name VARCHAR(50) -- database name 
2DECLARE @path VARCHAR(256) -- path for backup files 
3DECLARE @fileName VARCHAR(256) -- filename for backup 
4DECLARE @fileDate VARCHAR(20) -- used for file name 
5
6SET @path = 'C:\Backup\' 
7
8SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
9
10DECLARE db_cursor CURSOR FOR 
11SELECT name 
12FROM MASTER.dbo.sysdatabases 
13WHERE name NOT IN ('master','model','msdb','tempdb') 
14
15OPEN db_cursor  
16FETCH NEXT FROM db_cursor INTO @name  
17
18WHILE @@FETCH_STATUS = 0  
19BEGIN  
20      SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
21      BACKUP DATABASE @name TO DISK = @fileName 
22
23      FETCH NEXT FROM db_cursor INTO @name 
24END 
25
26CLOSE db_cursor  
27DEALLOCATE db_cursor 

1
2        
3            
4        
5     DECLARE 
6    @product_name VARCHAR(MAX), 
7    @list_price   DECIMAL;
8
9DECLARE cursor_product CURSOR
10FOR SELECT 
11        product_name, 
12        list_price
13    FROM 
14        production.products;
15
16OPEN cursor_product;
17
18FETCH NEXT FROM cursor_product INTO 
19    @product_name, 
20    @list_price;
21
22WHILE @@FETCH_STATUS = 0
23    BEGIN
24        PRINT @product_name + CAST(@list_price AS varchar);
25        FETCH NEXT FROM cursor_product INTO 
26            @product_name, 
27            @list_price;
28    END;
29
30CLOSE cursor_product;
31
32DEALLOCATE cursor_product;
33Code language: SQL (Structured Query Language) (sql)

1A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.