We help IT Professionals succeed at work.
Get Started

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

287 Views
Last Modified: 2013-11-10
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

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE