--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
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