Problem trying to test for existence of a column inside table conditional column select

Hi all,

I am trying to test for the existence of a column within a table.  The reason for this is we have two different versions of our gaming system 1 of the versions has the column whereas the other does not.  I really would like to avoid having two different versions of my scripting out there and run the same VB scripts and crystal reports & templates out there.

My thought was this, test for the columns existence, if it exists then select that column with a isnull handler, if it does not, then select 0 to fill in 0's in the results.

I am having a problem with the syntax on that and could use help, below is my code, I seperated the lines where I am having the most difficulty.

Thank you again!

use winoasis
DECLARE		@Casino			VARCHAR(254),
		@VAR			INT
SET		@Casino 	= 	(SELECT RIGHT(CASINONAME, LEN(CASINONAME)-9) FROM CDS_CONFIG (nolock))
if exists 	(select * from INFORMATION_SCHEMA.COLUMNS 
where 		TABLE_NAME='CDS_SLOTMAST' and COLUMN_NAME='MaxCoins' )
BEGIN
SELECT @VAR = '1'
END
if not exists 	(select * from INFORMATION_SCHEMA.COLUMNS 
where 		TABLE_NAME='CDS_SLOTMAST' and COLUMN_NAME='MaxCoins' )
BEGIN
SELECT @VAR = '0'
END
 
select		@CASINO as Casino,
		a.slotnumber,
		a.locationstring,
		IsNull(a.nReels, ' ') as nReels,
		IsNull(a.Reels, ' ') as Reels,
		IsNull(a.nCoins, ' ') as nCoins,
		IsNull(a.nPaylines, ' ') as nPaylines,
		IsNull(a.MaxHandPay, '.00') as MaxHandPay,
		CASE 
		WHEN SUM(@VAR) = 1 THEN (SELECT IsNull(a.MaxCoins, '0'))
    		WHEN SUM(@VAR) = 0 THEN 0
    		END AS MaxCoins,
 
		IsNull(a.SealNumber, 'None') as SealNumber,
		IsNull(a.SerialNumber, 'None') as SerialNumber,
		IsNull(a.EpromId, 'None') as EpromId,
		IsNull(a.EpromId2, 'None') as EpromId2,
		a.denomination,
		b.cabinetdesc,
		c.styledesc,
		d.manufacturer,
		a.description,
		IsNull(g.CalcDesc, 'None') as CalcID,
		IsNull(e.leasedesc, 'Not Leased') as LeaseInfo,
		IsNull(f.ProgDesc, 'Non-Progressive') as ProgressiveType,
		IsNull(h.Description, 'None') as PointCalc_ID,
		a.active
from		cds_slotmast A (nolock)
inner join	bb_cabinet B (nolock) on a.cabinet_id = b.cabinet_id
inner join	bb_style C (nolock) on a.style_id = c.style_id
inner join	bb_mfr D (nolock) on a.mfr_id = d.mfr_id
left outer join	bb_lease E (nolock) on a.lease_id = e.lease_id
left outer join	bb_ProgressiveType F (nolock) on a.ProgressiveType_ID = f.ProgressiveType_ID
left outer join	bb_Calc G (nolock) on a.Calc_ID = G.Calc_ID
left outer join	cds_pointcalc H (nolock) on a.PointCalc_ID = H.PointCalc_ID
where		a.active = 'Y'	and a.currentrevision = 'y'
END

Open in new window

LVL 1
smyers051972Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dportasCommented:
You can't do that because SQL still needs to validate the column name. You can't short-circuit that if you use static SQL scripts.

If you'd used stored procedures you could have created a different proc that rolled out with each version of the database. Is there some reason why you don't do that? Why would you want to put a query in a script?
0
smyers051972Author Commented:
The issue is merely a single column exists in 1 DB and the other DB everything else BUT that 1 column (CDS_SLOTMAST.MaxCoins) does exist.

I created that SQL query to be run as a command in crystal which then in turn exports the data to excel.  I use crystal as sort of a helper to export to excel.

I am unsure why we can not have two versions of that query however? IF-THEN-ELSE type statement ???
0
smyers051972Author Commented:
Let me add this:

I guess what would help is if we can have SQL do a conditional evaluation, if the column exists in the table, run the query this way, if the column does not exist run it the other way, I dont mind having two versions of the query existing in the same query but I would have to duplicate like 5 different scripts and crystal reports to accomodate this with out a conditional column selection.

The DB data is essentially the same, our gaming provider simply changed the structure of that table not to include MaxCoins column for whatever reason.
0
dro_lawCommented:
Would this help?

DECLARE @COLCOUNT INT;

set @COLCOUNT = 0

SELECT @COLCOUNT = COUNT(NAME) FROM SYSCOLUMNS WHERE NAME = <COLUMN NAME HERE> AND ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = <INSERT TABLENAME HERE>)

IF @COLCOUNT > 0
    BEGIN
        <WRITE FIRST SELECT STATEMENT HERE>
    END

IF @COLCOUNT = 0
    BEGIN
         <WRITE SECOND SELECT STATEMENT HERE>
    END

You may have to adjust that a little as it's pseudocode, but the concept should work.
0
dportasCommented:
I'd suggest you create stored procedures to drive your reporting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.