/SQL/What is cursor in sql server with example

What is cursor in sql server with example

Cursor in sql server

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
*/

Source: grepper.com

MSSQL CURSOR

/* DECLARA EL CURSOR PARA EL id de product */
/* Y CARGA EN MEMORIA */
DECLARE ProdId CURSOR FOR SELECT id FROM product
/* DECLARA VARIABLE DONDE SE ALMACENARA */
DECLARE @identificador INT
/* UTILITZA EN MEMORIA*/
OPEN ProdId 
/* AGAFA EL PRIMER VALOR */
FETCH next FROM ProdInfo INTO @identificador
WHILE @@fetch_status = 0
BEGIN
    PRINT @identificador
    FETCH NEXT FROM ProdId INTO @identificador
END
/* TANCA UTILITZACIO */
CLOSE  ProdId 
/* ELIMINA DE MEMORIA */
DEALLOCATE ProdId

Source: bluegraded.i234.me

What is purpose of cursor in sql

A 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.

Source: grepper.com

What is cursor in sql server with example

/*
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;

Source: sqlservertutorial.net

Attributes of cursor in sql

/*
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;

Source: grepper.com

SQL Cursor

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

Source: grepper.com

Similar codes

20 votes390 views1 answer
Get all table name in sql through php
SQL
0 vote456 views1 answer
Play framework connection via windows sql server
SQL
1 vote394 views3 answers
How to pass password to mysql command line
SQL
6 votes365 views2 answers
Psql load dump
SQL
1 vote341 views2 answers
T-sql random number for each row
SQL
2 votes375 views2 answers
Sql query to copy data from one column to another
SQL
0 vote372 views1 answer
Grant privileges mysql to database 1064
SQL
1 vote386 views1 answer
How to select all attributes from a row if there is a certain string in it MySQL
SQL
0 vote379 views1 answer
Intellij idea add mysql connector
SQL
0 vote361 views2 answers
How to generate a unique random number in mysql
SQL