Stored Proc With Cursor Loop

Create Proc

The cursor will iterate and return its position each time, only once it returns 0 is it complete. Looping like this was useful if the resulting insert needed to trigger another proc.

READ_ONLY Prevents updates made through this cursor.

FORWARD_ONLY Specifies that the cursor can only move forward and be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option.

LOCAL Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.

STATIC Specifies that the cursor always displays the result set as it was when the cursor was first opened, and makes a temporary copy of the data to be used by the cursor

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
CREATE OR ALTER PROCEDURE [dbo].[prc_run_some_crap]
(
	@id			                        BIGINT,
	@unused_second_param_id			    BIGINT
)
AS 

BEGIN
	
	PRINT '@id=' + CONVERT(VARCHAR(10), @id);
	PRINT '@unused_second_param_id=' + CONVERT(VARCHAR(10), @unused_second_param_id);

	DECLARE @some_id int, 
	@some_description varchar(10), 
	@some_date datetime, 
	@some_bool bit;  

	DECLARE My_Sweet_Cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR  

		SELECT 
			some_id, some_description, some_date, some_bool
		FROM 
			 dbo.[first_table] WITH (NOLOCK)
		WHERE 
			id = @id;

	OPEN My_Sweet_Cursor;  

		FETCH NEXT 
		FROM My_Sweet_Cursor
		INTO @some_id, some_description, some_date, some_bool;

	WHILE @@FETCH_STATUS = 0  
	BEGIN 
	
		IF NOT EXISTS(SELECT some_id FROM [dbo].[second_table] WITH (NOLOCK) WHERE some_id=@some_id AND some_description=@some_description)
			BEGIN  
				PRINT 'Copying record : ' + CONVERT(VARCHAR(10), @some_id);
				INSERT INTO [dbo].[second_table]
				([some_id]
				,[some_description]
				,[some_date]
				,[some_bool])
				VALUES
				(@some_id, 
				@some_description, 
				@some_date,
				@some_bool);
			END; 
		ELSE
			BEGIN 
				PRINT 'Already exists ' + CONVERT(VARCHAR(10), @some_id);
			END;
			
		FETCH NEXT 
		FROM My_Sweet_Cursor
		INTO @some_id, some_description, some_date, some_bool;	
		
	END;

	CLOSE My_Sweet_Cursor;  
	DEALLOCATE My_Sweet_Cursor;  

END;
GO

Execute Proc

1
EXEC dbo.[prc_run_some_crap] @id = 123, @second_param_id = 456;