Wednesday 2 April 2014

how to write case statement if column exist then return same column value other wise return null?

--Creating work table test11

if exists(select * from sys.tables where name='test11')
drop table  test11
go

 CREATE TABLE test11
  (
     ID INT
  )

go
--Creating function

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'fnColumnname')
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION fnColumnname
GO
CREATE FUNCTION fnColumnname(@Table_Name  NVARCHAR(100),
                          @Column_Name NVARCHAR(100))
returns VARCHAR(200)
AS
  BEGIN
      IF EXISTS (SELECT*
                 FROM   sys.columns
                 WHERE  Object_name(object_id) = @Table_Name
                        AND name = @Column_Name)
        BEGIN
            RETURN
              (SELECT @Table_Name + '.' + @Column_Name)
        END

      RETURN 'NULL'
  END
 go

  --Creating table for columns

 IF OBJECT_ID('tempdb..#Column_exists') IS NOT NULL
  DROP TABLE #Column_exists
go
SELECT name
INTO   #Column_exists
FROM   sys.columns
WHERE  Object_name(object_id) = 'test11'
go

 INSERT INTO test11
VALUES      (1)
go

Declare @lclsql varchar(max)
SET @lclsql='select  case  when (select count(*) from #Column_exists where name=''ID'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'ID')
       + ' end  ID ,case  when (select count(*) from #Column_exists where name=''NAME'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'NAME')
       + ' end  NAME,case  when (select count(*) from #Column_exists where name=''SAL'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'SAL')
       + ' end  SAL,case  when (select count(*) from #Column_exists where name=''DEPTNO'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'DEPTNO')
       + ' end  DEPTNO' + ' from test11;'

 exec(@lclsql)
--Result:
-- Now we have only one field and so other field will be NULL  
     
  go

 --Adding Name column
 alter table test11
 add  NAME varchar(100)

go

 IF OBJECT_ID('tempdb..#Column_exists') IS NOT NULL
  DROP TABLE #Column_exists

go

SELECT name
INTO   #Column_exists
FROM   sys.columns
WHERE  Object_name(object_id) = 'test11'
go

 INSERT INTO test11
VALUES      (1,'Shrikant')

go

Declare @lclsql varchar(max)
SET @lclsql='select  case  when (select count(*) from #Column_exists where name=''ID'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'ID')
       + ' end  ID ,case  when (select count(*) from #Column_exists where name=''NAME'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'NAME')
       + ' end  NAME,case  when (select count(*) from #Column_exists where name=''SAL'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'SAL')
       + ' end  SAL,case  when (select count(*) from #Column_exists where name=''DEPTNO'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'DEPTNO')
       + ' end  DEPTNO' + ' from test11;'


exec( @lclsql)

go


No comments:

Post a Comment

First Database In Sql Server