Column doesn't exist - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Column doesn't exist

Author

Topic

jandh98
Starting Member

10 Posts

Posted - 2010-11-26 : 13:19:10

I'm writing a query that will be used by mulitple clients with different databases. On a recent update, a database change was made and a field was added. I'd like to call that new field in my query. However, some of our clients on a later version don't yet have this field. I don't want to add the field to the database because that will be handled by a later install, but I would like to pull the field if it's there... Anyhow... here's an example:

Select
ID,
Postedby

From
table

The problem is - the older clients don't have the postedby column.

What I'd like is if that field doesn't exist - to put a 0 or some identifier in there instead. I've played around with different options and can't figure out the answer.

Is this possible?

TIA!
Heather

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 13:38:27

The clients would need all need the column, otherwise you have to make the query dynamic with first checking the schema to see if the column exists.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 02:14:06

is it a view that cilents are using which doesnt have this column?

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

MohanKandasamy
Starting Member

9 Posts

Posted - 2010-11-27 : 08:51:24

Hi

Please try this

declare @colNameActultoCheck Varchar(100) = 'ID' -- which column you need to check

declare @tblNameActultoCheck Varchar(100) = 'tbl' -- which table you need to check
declare @colA varchar(100)
declare @colB varchar(100)

SET @colA = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tblNameActultoCheck AND COLUMN_NAME=@colNameActultoCheck)

declare @sqlQuery Varchar(2000)

IF (@colA <> '')
SET @sqlQuery = 'SELECT ' + @colA + ' FROM TBL'
ELSE

SET @sqlQuery = 'SELECT 0 as ID FROM TBL'

EXEC (@sqlQuery)

Mohan Kandasamy

Kristen
Test

22859 Posts

Posted - 2010-11-27 : 10:11:48

I would create a view, and then update the view when the new version of the software is rolled out (to reference the actual colunm)

Many of our queries have:


SELECT Col1, Col2, ...
FROM MyTable AS T
   JOIN MyTable_VIEW AS V
        ON V.V_ID = T.ID

we may have a column "Reserved5" in MyTable, but we make this available as "MyNewColumnName" in the VIEW - thus the application does not need to change when Reserve5 is renamed to MyNewColumnName (and dropped from the VIEW).

We tend to have some Code-Lookups in the Table, so the View provides a JOIN to the Description tables for them too.

Kristen
Test

22859 Posts

Posted - 2010-11-27 : 10:14:07

"SET @colA = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tblNameActultoCheck AND COLUMN_NAME=@colNameActultoCheck)"

Does INFORMATION_SCHEMA.COLUMNS expose the column if the code is in an SProc and the currently logged on user does not have SELECT permissions on the table?

Not sure of the answer, but I think this has come up before that the INFORMATION_SCHEMA tables may not show data that the user does not directly have access to.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources