Link to home
Start Free TrialLog in
Avatar of smyers051972
smyers051972Flag for United States of America

asked on

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

Avatar of dportas
dportas

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?
Avatar of smyers051972

ASKER

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 ???
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of dportas
dportas

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial