Cursor in SQL Server (with example)

What is a Cursor?

Cursor is a variable in SQL Server Database which is used for row-by row operations. It indicates the current position in the result set so that it is known as Cursor.

Script for taking backup of all databases by using cursor.

DECLARE @dbName VARCHAR(50) --DATABASE NAME

DECLARE @path VARCHAR(500)  --BACKUP PATH

DECLARE @fileName VARCHAR(100) --FILE NAME FOR BACKUP

DECLARE @fileDate VARCHAR(100) --FILE DATE FOR NAME WITH FILE NAME

SET @path = 'C:\Backup\'       --BACKUP PATH

SELECT @fileDate = CONVERT(VARCHAR, GETDATE(),105)

DECLARE dbName_cursor CURSOR FOR   --DECLARATION AND INITILIZING CURSOR

SELECT [name] FROM [master].[dbo].[sysdatabases]

WHERE [name] NOT IN ('master','model','msdb','tempdb')

OPEN dbName_cursor        --OPEN CURSOR

FETCH NEXT FROM dbName_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @fileName = @path+@dbName+'_'+@fileDate+'.bak'

    BACKUP DATABASE @dbName TO DISK = @fileName

   

    FETCH NEXT FROM dbName_cursor INTO @dbName

END

CLOSE dbName_cursor      --CLOSING CURSOR

DEALLOCATE dbName_cursor --DEALLOCATION MEMORY

JOIN in SQL SERVER

JOIN

Join enable to retrieve data from two or more tables based on logical relationships between the tables.

--TABLE JOIN CONCEPT--

INNER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 INNER JOIN TB2 T2 ON T1.ID=T2.ID

LEFT OUTER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 LEFT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

RIGHT OUTER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 RIGHT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

FULL OUTER JOIN

SELECT T1.*, T2.*

FROM TB1 T1 FULL OUTER JOIN TB2 T2 ON T1.ID=T2.ID

CROSS JOIN

<no picture>

SELECT T1.*, T2.*

FROM TB1 T1 CROSS JOIN TB2 T2

--(both are equivalent)--

SELECT T1.*, T2.*

FROM TB1 T1 INNER JOIN TB2 T2 ON 1=1

LEFT OUTER JOIN - WHERE IS NULL

SELECT T1.*, T2.* FROM TB1 T1 LEFT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

WHERE T2.ID IS NULL

RIGHT OUTER JOIN - WHERE IS NULL

SELECT T1.*, T2.* FROM TB1 T1 RIGHT OUTER JOIN TB2 T2 ON T1.ID=T2.ID

WHERE T1.ID IS NULL

FULL OUTER JOIN - WHERE IS NULL

SELECT T1.*, T2.* FROM TB1 T1 FULL OUTER JOIN TB2 T2 ON T1.ID=T2.ID

WHERE T1.ID IS NULL OR T2.ID IS NULL 

SELF JOIN - (A REAL EXAMPLE)

SELECT T1.id, T1.name, T2.name AS manager_name

FROM employee T1 left outer JOIN employee t2

ON T1.managerId=T2.id