Thursday, 19 April 2012

Script for swapping gender in sql server through Cursor


Step 1:Create table ,here GenderConvert

--create table GenderConvert(name nvarchar(20),gender nvarchar(1))

Step 2:Insert some Values in table

--insert into genderconvert values('ashish', 'm' )
--insert into genderconvert values('sonam', 'f' )
--insert into genderconvert values('priya', 'f' )
--insert into genderconvert values('sonu', 'm' )
--insert into genderconvert values('anshu', 'f' )
--insert into genderconvert values('vinod', 'm' )
--insert into genderconvert values('pappu ', 'm' )
--insert into genderconvert values('pavan', 'm' )
--insert into genderconvert values('priynka', 'f' )
--insert into genderconvert values('sonali', 'f' )
--insert into genderconvert values('guru', 'm' )

STEP 3:Execute this script (cursor)

DECLARE @name nvarchar(20)
DECLARE @gender nvarchar(1)
DECLARE @MyCursor CURSOR

--Assign table to cursor variable

SET @MyCursor = CURSOR FOR 
SELECT name,gender FROM genderconvert

--Open cursor

OPEN @MyCursor

--Fetch cursor row on by one

FETCH NEXT 
FROM @MyCursor INTO @name , @gender
WHILE @@FETCH_STATUS = 0
BEGIN

if(@gender = 'm' )
BEGIN 
UPDATE  genderconvert set gender='f' where name=@name
END
else
BEGIN 
UPDATE  genderconvert set gender='m' where name=@name
END
FETCH NEXT FROM @MyCursor into  @name,@gender
END


           After executing this script gender will be swap.Please give your valuable review if this post helpful for you.