Swap column value of table using Cursor in SQL Server 2008

Swap column value of table using Cursor in SQL Server 2008

As idea to write an article came from last day when one of my friends encountered a question in an interview. I feel it would be a good brain teaser.

Question - > Please swap a value of specific column value with other. For an example if we have a table t1 having column Gender than Male should be replaced with Female and vice versa.

Note: You should have little knowledge of Cursor and Switch Statement in SQLServer2005,2008 so on.

I’ve taken an idea and created a table keeps the structure as depicted below:

select * from customers

Here we will swap name column values like “Sachin” will be replaced by “dotnetpiper.com” and vice versa.

Output will be like this.

I’ve used cursor to do the desired task .Reason to choose cursor because of it will fetch each row individually and will perform the desired action. Here is the actual SQL query implementation as

  1: DECLARE @name VARCHAR(50) -- database name  
  2: DECLARE DotnetPiper_Cursor CURSOR FOR  
  3: SELECT name 
  4: FROM customers
  5: 
  6: OPEN DotnetPiper_Cursor   
  7: FETCH NEXT FROM DotnetPiper_Cursor INTO @name   
  8: 
  9: WHILE @@FETCH_STATUS = 0   
 10: BEGIN         
 11:       Update customers SET name=( Case when @name='sachin' then 'dotnetpiper.com'
 12:                                        when @name= 'dotnetpiper.com' then 'sachin'
 13:                                         else @name 
 14:                                         End) WHERE CURRENT OF DotnetPiper_Cursor
 15: 
 16:        FETCH NEXT FROM DotnetPiper_Cursor INTO @name   
 17: END   
 18: 
 19: CLOSE DotnetPiper_Cursor   
 20: DEALLOCATE DotnetPiper_Cursor

SQL Snippet to create table Customer table:

  1: USE [Employee]
  2: GO
  3: 
  4: /****** Object:  Table [dbo].[Customers]    Script Date: 08/03/2015 07:18:12 ******/
  5: SET ANSI_NULLS ON
  6: GO
  7: 
  8: SET QUOTED_IDENTIFIER ON
  9: GO
 10: 
 11: SET ANSI_PADDING ON
 12: GO
 13: 
 14: CREATE TABLE [dbo].[Customers](
 15: [ID] [int] NULL,
 16: [Name] [varchar](50) NULL,
 17: [Salary] [varchar](50) NULL
 18: ) ON [PRIMARY]
 19: 
 20: GO
 21: 
 22: SET ANSI_PADDING OFF
 23: GO
 24: 
 25: 
 26: 

 

I wish it will help you in order to crack such question or scenario.

Note: Please share you opinion and advise us for better approach also.I really appreciate you initiation

To know more MVC and WebApi Kindly go through with these links

MVC Articles & WCF and WebApi

Thanks.
Enjoy coding and reading.

Categories: Interview Questions , SQL Server