xxxxxxxxxx
IF OBJECT_ID('tempdb.dbo.#employees', 'U') IS NOT NULL drop table tempdb.dbo.#employees
create table #employees (id int)
insert into #employees
select 1 union select 2 union select 3 union select 4 union select 5
--CURSOR
declare @id int
declare @ids cursor
set @ids = cursor
for
select id from #employees
open @ids
fetch next from @ids into @id
while @@FETCH_STATUS = 0 --can add more conditions
begin
select @id --process with cursor data
fetch next from @ids into @id
end
close @ids
deallocate @ids
xxxxxxxxxx
DECLARE @TempTable AS TABLE (Id INT, [Name] VARCHAR(200));
INSERT INTO @TempTable
VALUES (1,'Test Name1'),
(2,'Test Name2'),
(3,'Test Name2')
DECLARE
@Id INT,
@Name VARCHAR(200);
DECLARE cursor_personInfo CURSOR
FOR SELECT
Id,
[Name]
FROM
@TempTable;
OPEN cursor_personInfo;
FETCH NEXT FROM cursor_personInfo INTO
@Id,
@Name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(VARCHAR(10),@Id) +' '+ @Name;
FETCH NEXT FROM cursor_personInfo INTO
@Id,
@Name;
END;
CLOSE cursor_personInfo;
DEALLOCATE cursor_personInfo;
/*
Declare – Declares the cursor with a name and the select statement which populates the result set
Open – Opens a cursor and populates the cursor by executing the select statement which is specified while declaring a cursor
Fetch – To retrieve a specific row from the cursor based on the fetch arguments like NEXT, FIRST, LAST, etc
Close – Closes the current result set of SQL Server cursor and can be reopened
Deallocate – Removes cursor reference and releases all the resources associated with a cursor
*/
xxxxxxxxxx
/*
Declare – Declares the cursor with a name and the select statement which populates the result set
Open – Opens a cursor and populates the cursor by executing the select statement which is specified while declaring a cursor
Fetch – To retrieve a specific row from the cursor based on the fetch arguments like NEXT, FIRST, LAST, etc
Close – Closes the current result set of SQL Server cursor and can be reopened
Deallocate – Removes cursor reference and releases all the resources associated with a cursor
*/
ECLARE @TempTable AS TABLE (Id INT, [Name] VARCHAR(200));
INSERT INTO @TempTable
VALUES (1,'Test Name1'),
(2,'Test Name2'),
(3,'Test Name2')
DECLARE
@Id INT,
@Name VARCHAR(200);
DECLARE cursor_personInfo CURSOR
FOR SELECT
Id,
[Name]
FROM
@TempTable;
OPEN cursor_personInfo;
FETCH NEXT FROM cursor_personInfo INTO
@Id,
@Name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(VARCHAR(10),@Id) +' '+ @Name;
FETCH NEXT FROM cursor_personInfo INTO
@Id,
@Name;
END;
CLOSE cursor_personInfo;
DEALLOCATE cursor_personInfo;
xxxxxxxxxx
/*
Declare – Declares the cursor with a name and the select statement which populates the result set
Open – Opens a cursor and populates the cursor by executing the select statement which is specified while declaring a cursor
Fetch – To retrieve a specific row from the cursor based on the fetch arguments like NEXT, FIRST, LAST, etc
Close – Closes the current result set of SQL Server cursor and can be reopened
Deallocate – Removes cursor reference and releases all the resources associated with a cursor
*/
DECLARE @TempTable AS TABLE (Id INT, [Name] VARCHAR(200));
INSERT INTO @TempTable
VALUES (1,'Test Name1'),
(2,'Test Name2'),
(3,'Test Name2')
DECLARE
@Id INT,
@Name VARCHAR(200);
DECLARE cursor_personInfo CURSOR
FOR SELECT
Id,
[Name]
FROM
@TempTable;
OPEN cursor_personInfo;
FETCH NEXT FROM cursor_personInfo INTO
@Id,
@Name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(VARCHAR(10),@Id) +' '+ @Name;
FETCH NEXT FROM cursor_personInfo INTO
@Id,
@Name;
END;
CLOSE cursor_personInfo;
DEALLOCATE cursor_personInfo;
xxxxxxxxxx
-- Declare the cursor
DECLARE my_cursor CURSOR FOR
SELECT name, email
FROM customers;
-- Open the cursor
OPEN my_cursor;
-- Fetch the first row from the cursor
FETCH NEXT FROM my_cursor;
-- Loop through all rows in the result set
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the values of the columns in the current row
PRINT 'Name: ' + name + ', Email: ' + email;
-- Fetch the next row
FETCH NEXT FROM my_cursor;
END
-- Close and deallocate the cursor
CLOSE my_cursor;
DEALLOCATE my_cursor;
xxxxxxxxxx
DECLARE @CONTACTID INT
--will need to declare at least one variable
--in this case, to store values when iterating through cursor
DECLARE SIMPLE_CURSOR CURSOR FOR
SELECT ID
FROM CONTACT
OPEN SIMPLE_CURSOR
FETCH NEXT FROM SIMPLE_CURSOR --Start the cursor
INTO @CONTACTID
WHILE @@FETCH_STATUS = 0 --while there is a loaded record, keep processing
BEGIN
--do whatever you need to do
print ('This is where the magic happens!
Do whatever you need to do (update/insert/delete/stored proc/etc.')
FETCH NEXT FROM SIMPLE_CURSOR INTO @CONTACTID --fetch next record
END
CLOSE SIMPLE_CURSOR --close and deallocate
DEALLOCATE SIMPLE_CURSOR