WHAT IS CURSOR?
In SQL Server, the Cursor walks through the recordset, which is returned as a select result, to perform a transaction on each row.
You can use it with Stored Procedure or Trigger or you can run TSQL code from within a job.
Let’s make a simple example to understand what a cursor is and how to use it.
Let’s write a script that sends a congratulatory mail to students who have successfully completed their education in an educational institution.
Our student information is kept in the following table.
Let’s send a congratulatory mail to the students who are on grade 70 or above and send an e-mail to the students who are below the score of 70 that they can not finish the course successfully.
You need to configure Database Mail to send mail via Database Mail.
You can benefit from the article titled “How To Configure Database Mail On SQL Server“.
CREATE A TABLE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE [dbo].[Student]( [Id] [int] IDENTITY(1,1) NOT NULL, [StudentName] [varchar](50) NULL, [StudentSurname] [varchar](50) NULL, [StudentEmail] [varchar](50) NULL, [StudentScore] [int] NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] |
After creating the table, I will add 3 records to my table using the following script.
INSERTING RECORDS TO THE TABLE
|
1 2 3 4 5 6 7 8 |
INSERT INTO [dbo].[Student]([StudentName],[StudentSurname],[StudentEmail],[StudentScore]) VALUES('Nurullah','CAKIR','nurullah.ckr@gmail.com',65) INSERT INTO [dbo].[Student]([StudentName],[StudentSurname],[StudentEmail],[StudentScore]) VALUES('FARUK','ERDEM','y@gmail.com',90) INSERT INTO [dbo].[Student]([StudentName],[StudentSurname],[StudentEmail],[StudentScore]) VALUES('DİLARA','AYDIN',' x@gmail.com ',90) |
Then, using the cursor, go through the records in the student table one by one and check the score when we get to each record, and if it is over 70, we will send a congratulatory mail to that student and a failure mail below 70.
Within the code I have written explanations for easier understanding between / ** / expressions.
CREATING 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 |
DECLARE @StudentName VARCHAR(50) DECLARE @StudentSurname VARCHAR(50) DECLARE @StudentEmail VARCHAR(50) DECLARE @StudentScore int DECLARE @HTML VARCHAR(MAX) ; /*We give a name to Cursor*/ DECLARE CursorExample CURSOR FOR /*The select clause that specifies the recordset that the Cursor is going to loop around.*/ SELECT [StudentName],[StudentSurname],[StudentEmail],[StudentScore] FROM [Student] OPEN CursorExample FETCH NEXT FROM CursorExample INTO @StudentName,@StudentSurname,@StudentEmail,@StudentScore /*WHILE @@FETCH_STATUS=0 means to move to the next record until there is no record to traverse on the Cursor.*/ WHILE @@FETCH_STATUS =0 BEGIN /*In the code block between "IF (@ StudentScore> = 70)" and "BEGIN-END" below, if the student's grade is greater than 70, we send a congratulatory mail to that student.*/ IF(@StudentScore>=70) BEGIN SET @HTML = N'<H1>Dear'+@StudentName+' '+@StudentSurname+',<br></H1>' + N'You have successfully completed our course ,<br>' + N'Congratulations.<br>' EXEC msdb.dbo.sp_send_dbmail @recipients=@StudentEmail, @subject = 'Course Success Information', @body = @HTML, @body_format = 'HTML' ; END /*In the code block between "ELSE" and "BEGIN-END" below, if the student's grade is less than 70, we send a failure mail to that person*/ ELSE BEGIN SET @HTML = N'<H4>Dear'+@StudentName+' '+@StudentSurname+',<br></H4>' + N'You Could Not Complete Our Course Successfully,<br>' + N'You can rejoin the course.<br>' EXEC msdb.dbo.sp_send_dbmail @recipients=@StudentEmail, @subject = 'Course Success Information', @body = @HTML, @body_format = 'HTML' ; END FETCH NEXT FROM CursorExample INTO @StudentName,@StudentSurname,@StudentEmail,@StudentScore END /*We close the cursor with CLOSE and DEALLOCATE commands*/ CLOSE CursorExample DEALLOCATE CursorExample |
