add column to view if column exists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

add column to view if column exists

Author

Topic

BATTY_1
Starting Member

2 Posts

Posted - 2012-06-21 : 12:23:04

Hi. Hope someone can help.

I am trying to create a view that makes sure if one of two or both columns exists in a table then include it in the view. I have used add new view in my sql database as the starting point.

Here is my script that does not check for the columns

select METAID, A46 AS Surname, A36 as PolicyNumber
From dbo.TU22_ATTRIBUTE
union
select METAID, A46 AS Surname, A36 as PolicyNumber
From dbo.TU23_ATTRIBUTE

What I would like to do is check if A46 or A36 or both exist then include it in the view, if not just ignore it.

Thanks in Advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-21 : 13:01:14

A view is static. It can't be conditional on the existence of a column.
You can generate the view and make that creation conditional.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 17:46:25

quote:
Originally posted by BATTY_1

Hi. Hope someone can help.

I am trying to create a view that makes sure if one of two or both columns exists in a table then include it in the view. I have used add new view in my sql database as the starting point.

Here is my script that does not check for the columns

select METAID, A46 AS Surname, A36 as PolicyNumber
From dbo.TU22_ATTRIBUTE
union
select METAID, A46 AS Surname, A36 as PolicyNumber
From dbo.TU23_ATTRIBUTE

What I would like to do is check if A46 or A36 or both exist then include it in the view, if not just ignore it.

Thanks in Advance

do you mean columns themselves exist in table or do you mean data exist in those columns? former is not something you can do inside view as once created its metadata wont change unless you alter it later
Latter i dont think you need to worry as if data is not there it will return values as '' or NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BATTY_1
Starting Member

2 Posts

Posted - 2012-06-22 : 04:21:42

quote:
Originally posted by nigelrivett

A view is static. It can't be conditional on the existence of a column.
You can generate the view and make that creation conditional.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for that! (Both). Total lack of experience on that one. When you say generate the view, do you mean using "Create View as"? I have not used that before but will give it a go.

Once again Thanks

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:38:13

yep...you can generate view script based on existence condition like


IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'your view name' AND TABLE_SCHEMA = 'Your schema name' AND TABLE_TYPE = 'View')
  DROP VIEW youviewname
GO
CREATE VIEW youviewname
AS
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources