Invalid Column name? - 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)

Invalid Column name?

Author

Topic

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-11 : 09:39:06

Hey guys, I am getting an error in my T-SQL query you guys helped me with. I added one more variable, and whatever i put in when i run the query, it's telling me the value i entered to search for is an invalid column name... here's the query results:

SELECT
r.ListingType,
r.REListingID,
r.REListingDate,
r.Username,
r.ZipCode,
r.ListingType,
r.StudioFlag,
r.BRFlag1,
r.BRFlag2,
r.BRFlag3,
r.BRFlag4,
r.OverBRFlag4,
r.CondoFlag,
a.WindowAir,
a.CentralAir,
a.BalconyDeckPatio,
a.UseOfYard,
a.Dishwasher,
a.WasherDryer,
a.Fireplace,
a.EIK,
a.HardwoodFloors,
a.BroadbandNet,
a.TV,
a.Thermostat,
a.LandlordNotPresent,
a.Smoking,
a.NoPetsAllowed,
a.Cat,
a.MoreCats,
a.SmallDog,
a.LargeDogs,
a.Doorperson,
a.IngroundPool,
a.AboveGroundPool,
a.Elevator,
a.UseOfGarage,
a.LaundryFacilities,
a.HealthCenter,
a.StorageAreas,
a.WheelchairAccess,
a.BusinessCenters,
a.RentCharge,
a.RentFrequency
FROM db_REListings as r
INNER JOIN db_RentalAmenities AS a ON a.REListingID = r.REListingID
WHERE 1 = 1
AND r.ListingType = Rental
Msg 207, Level 16, State 1, Line 50
Invalid column name 'Rental'.

Here's the T-SQL Query itself:

@ListingType nchar(10) = NULL,
@Studio INT = NULL,
@Br1 INT = NULL,
@Br2 INT = NULL,
@Br3 INT = NULL,
@Br4 INT = NULL,
@OverBr4 INT = NULL,
@Condo INT = NULL,
@WindowAir INT = NULL,
@CentralAC INT = NULL,
@BalconyDeckPatio INT = NULL,
@UseOfYard INT = NULL,
@Dishwasher INT = NULL,
@WasherDryer INT = NULL,
@Fireplace INT = NULL,
@EIK INT = NULL,
@HardwoodFloors INT = NULL,
@BroadbandNet INT = NULL,
@TV INT = NULL,
@Thermostat INT = NULL,
@LandlordNotPresent INT = NULL,
@Smoking INT = NULL,
@NoPetsAllowed INT = NULL,
@Cat INT = NULL,
@MoreCats INT = NULL,
@SmallDog INT = NULL,
@LargeDogs INT = NULL,
@Doorperson INT = NULL,
@IngroundPool INT = NULL,
@AboveGroundPool INT = NULL,
@Elevator INT = NULL,
@UseOfGarage INT = NULL,
@LaundryFacilities INT = NULL,
@HealthCenter INT = NULL,
@StorageAreas INT = NULL,
@WheelchairAccess INT = NULL,
@BusinessCenters INT = NULL,
@RentChargeMin INT = NULL,
@RentChargeMax INT = NULL,
@Debug BIT = 1
)
AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

SET @SQL = '
SELECT
r.ListingType,
r.REListingID,
r.REListingDate,
r.Username,
r.ZipCode,
r.ListingType,
r.StudioFlag,
r.BRFlag1,
r.BRFlag2,
r.BRFlag3,
r.BRFlag4,
r.OverBRFlag4,
r.CondoFlag,
a.WindowAir,
a.CentralAir,
a.BalconyDeckPatio,
a.UseOfYard,
a.Dishwasher,
a.WasherDryer,
a.Fireplace,
a.EIK,
a.HardwoodFloors,
a.BroadbandNet,
a.TV,
a.Thermostat,
a.LandlordNotPresent,
a.Smoking,
a.NoPetsAllowed,
a.Cat,
a.MoreCats,
a.SmallDog,
a.LargeDogs,
a.Doorperson,
a.IngroundPool,
a.AboveGroundPool,
a.Elevator,
a.UseOfGarage,
a.LaundryFacilities,
a.HealthCenter,
a.StorageAreas,
a.WheelchairAccess,
a.BusinessCenters,
a.RentCharge,
a.RentFrequency
FROM db_REListings as r
INNER JOIN db_RentalAmenities AS a ON a.REListingID = r.REListingID
WHERE 1 = 1
'

IF @Studio IS NOT NULL
SET @SQL = @SQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio)
IF @Br1 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1)
IF @Br2 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2)
IF @Br3 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3)
IF @Br4 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4)
IF @OverBr4 IS NOT NULL
SET @SQL = @SQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4)
IF @Condo IS NOT NULL
SET @SQL = @SQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo)
IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(VARCHAR(20), @ListingType)
IF @WindowAir IS NOT NULL
SET @SQL = @SQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir)
IF @CentralAC IS NOT NULL
SET @SQL = @SQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC)
IF @BalconyDeckPatio IS NOT NULL
SET @SQL = @SQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio)
IF @UseOfYard IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard)
IF @Dishwasher IS NOT NULL
SET @SQL = @SQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher)
IF @WasherDryer IS NOT NULL
SET @SQL = @SQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer)
IF @Fireplace IS NOT NULL
SET @SQL = @SQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace)
IF @EIK IS NOT NULL
SET @SQL = @SQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK)
IF @HardwoodFloors IS NOT NULL
SET @SQL = @SQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors)
IF @BroadBandNet IS NOT NULL
SET @SQL = @SQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet)
IF @TV IS NOT NULL
SET @SQL = @SQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV)
IF @Thermostat IS NOT NULL
SET @SQL = @SQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat)
IF @LandlordNotPresent IS NOT NULL
SET @SQL = @SQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent)
IF @Smoking IS NOT NULL
SET @SQL = @SQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking)
IF @NoPetsAllowed IS NOT NULL
SET @SQL = @SQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed)
IF @Cat IS NOT NULL
SET @SQL = @SQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat)
IF @MoreCats IS NOT NULL
SET @SQL = @SQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats)
IF @SmallDog IS NOT NULL
SET @SQL = @SQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog)
IF @LargeDogs IS NOT NULL
SET @SQL = @SQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs)
IF @Doorperson IS NOT NULL
SET @SQL = @SQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson)
IF @IngroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool)
IF @AboveGroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool)
IF @Elevator IS NOT NULL
SET @SQL = @SQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator)
IF @UseOfGarage IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage)
IF @LaundryFacilities IS NOT NULL
SET @SQL = @SQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities)
IF @HealthCenter IS NOT NULL
SET @SQL = @SQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter)
IF @StorageAreas IS NOT NULL
SET @SQL = @SQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas)
IF @WheelchairAccess IS NOT NULL
SET @SQL = @SQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess)
IF @BusinessCenters IS NOT NULL
SET @SQL = @SQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL
SET @SQL = @SQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin)
IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)

IF @Debug = 1
PRINT @SQL

EXEC (@SQL)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-11 : 09:40:13

[code]
ListingType = 'Rental'
[/code]

If you don't put the value you are looking for in single quotes, it interprets that as a column name.

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-11 : 13:43:53

ok, so how would i tell it to do that within the IF statement? Because it works for all the other IF's, just not this one.

IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(VARCHAR(20), @ListingType)

??

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-11 : 13:55:34

quote:
Originally posted by Mnemonic

ok, so how would i tell it to do that within the IF statement? Because it works for all the other IF's, just not this one.

IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(VARCHAR(20), @ListingType)

??

My bad, I just noticed that you are dynamically setting that part of the query above. It works for the others because those are setting up column names to pick from. The last one is a where clause so you need it to look like a value within the column itself

Change to this:


IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ''' + CONVERT(VARCHAR(20), @ListingType) + '''

You want to make sure you result from the SET statement passes @listingtype into the string with the single quotes around.

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-11 : 14:12:39

Thanks! that worked great! I had to add another single quote to the end, cause i have to close the otiginal single quote, but that was EXACTLY what i was looking for!

Thanks so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources