Cursor In RDBMS By Sagar Jaybhay 2020

In this article we will understand cursor in rdbms in our case we show example on SQL Server By Sagar Jaybhay . Also we will understand Merge statement in SQL Server and rerunnable SQL scripts and How to create a stored procedure with an optional parameter?

Cursors In RDBMS

In a relational database management system takes into consideration then it would process the data in sets inefficient manner. But when you have a need to process the data row by row basis then the cursor is the choice. The cursor is very bad at performance and it should be avoided and also you can replace the cursor with join.

Different Types of Cursors

• 1) Forward only
• 2) Static
• 3) Keyset
• 4) Dynamic

The cursor is loop through each record one by one so that’s why it’s performance is not good.

declare @empid int
declare @deptid int
declare @fullname varchar(200)

declare empcurose cursor for 
select EmpID,full_name,DepartmentID from Employee

open empcurose

fetch next from empcurose into @empid,@fullname,@deptid

while(@@FETCH_STATUS=0)
begin

print 'EmpID '+cast(@empid as varchar(10))+ ' Name '+cast(@fullname as varchar(100)) + ' deptid '+cast(@deptid as varchar(100))
fetch next from empcurose into @empid,@fullname,@deptid
end
close empcurose
deallocate empcurose

deallocate empcurose

This line is used to deallocate all resources which are allocated for that cursor.

What is rerunnable SQL scripts?

A re-runnable SQL script is a script that runs multiple times on the machine will not throw any kind of error. For example, if you use create table statement to create a table then use if not exist in create a statement so it will not throw an error.

How to create a stored procedure with an optional parameter?

create procedure searchemployee
@name varchar(10)=null,
@deptid int=null,
@gender varchar(10)=null
as
begin

if(@name is not null)
print 'i am in name '+cast(@name as varchar(20))
select * from tblEmp where [name]=@name;
return;

if(@deptid is not null)
print 'i am in deptid '+cast(@deptid as varchar(20))
select * from tblEmp where deptid=@deptid;
return;

if(@gender is not null)
print 'i am in gender '+cast(@gender as varchar(20))
select * from tblEmp where geneder=@gender;
return;
print 'i m here '+cast(@gender as varchar(20))+' '+cast(@deptid as varchar(20)) +' '+cast(@name as varchar(20))
select * from tblEmp

end

execute searchemployee @deptid=2

Simply pass default values to stored procedure variables.

Merge statement In SQL server

Merge statement is introduced in SQL server 2008 it allows to insert, update, deletes in one statement. It means there is no need to use multiple statements for insert update and delete. In this, if you want to use merge statement you need to 2 tables

• 1) Source table- it contains the changes that need to apply to the target table.
• 2) Target table- this is the table that requires changes insert, update, delete.

Merge statement joins the target table to source table by using a common column in both tables based on how you match up we perform insert, update and delete.