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 In RDBMS
Their are four types of cursors in rdbms which are listed below
- Forward only
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
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
- Source table– it contains the changes that need to apply to the target table.
- 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.