Can someone help with this T-SQL Real quick?

Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:

(@.StudioINT =NULL,@.Br1INT =NULL,@.Br2INT =NULL,@.Br3INT =NULL,@.Br4INT =NULL,@.OverBr4INT =NULL,@.CondoINT =NULL,@.ListingTypevarchar(10) =NULL,@.WindowAirINT =NULL,@.CentralACINT =NULL,@.BalconyDeckPatioINT =NULL,@.UseOfYardINT =NULL,@.DishwasherINT =NULL,@.WasherDryerINT =NULL,@.FireplaceINT =NULL,@.EIKINT =NULL,@.HardwoodFloorsINT =NULL,@.BroadbandNetINT =NULL,@.TVINT =NULL,@.ThermostatINT =NULL,@.LandlordNotPresentINT =NULL,@.SmokingINT =NULL,@.NoPetsAllowedINT =NULL,@.CatINT =NULL,@.MoreCatsINT =NULL,@.SmallDogINT =NULL,@.LargeDogsINT =NULL,@.DoorpersonINT =NULL,@.IngroundPoolINT =NULL,@.AboveGroundPoolINT =NULL,@.ElevatorINT =NULL,@.UseOfGarageINT =NULL,@.LaundryFacilitiesINT =NULL,@.HealthCenterINT =NULL,@.StorageAreasINT =NULL,@.WheelchairAccessINT =NULL,@.BusinessCentersINT =NULL,@.RentChargeMinINT =NULL,@.RentChargeMaxINT =NULL,@.DebugBIT = 1)AS SET NOCOUNT ONDECLARE @.SQLVARCHAR(8000)SET @.SQL ='SELECTr.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.RentFrequencyFROMdb_REListings as rINNER JOINdb_RentalAmenities AS a ON a.REListingID = r.REListingIDWHERE1 = 1'IF @.StudioISNOT NULLSET @.SQL = @.SQL +' AND r.StudioFlag = ' +CONVERT(VARCHAR(20), @.Studio)IF @.Br1ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag1 = ' +CONVERT(VARCHAR(20), @.Br1)IF @.Br2ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag2 = ' +CONVERT(VARCHAR(20), @.Br2)IF @.Br3ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag3 = ' +CONVERT(VARCHAR(20), @.Br3)IF @.Br4ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag4 = ' +CONVERT(VARCHAR(20), @.Br4)IF @.OverBr4ISNOT NULLSET @.SQL = @.SQL +' AND r.OverBRFlag4 = ' +CONVERT(VARCHAR(20), @.OverBr4)IF @.CondoISNOT NULLSET @.SQL = @.SQL +' AND r.CondoFlag = ' +CONVERT(VARCHAR(20), @.Condo)IF @.ListingTypeISNOT NULLSET @.SQL = @.SQL +' AND r.ListingType = ' +CONVERT(char, @.ListingType)IF @.WindowAirISNOT NULLSET @.SQL = @.SQL +' AND a.WindowAir = ' +CONVERT(VARCHAR(20), @.WindowAir)IF @.CentralACISNOT NULLSET @.SQL = @.SQL +' AND a.CentralAir = ' +CONVERT(VARCHAR(20), @.CentralAC)IF @.BalconyDeckPatioISNOT NULLSET @.SQL = @.SQL +' AND a.BalconyDeckPatio = ' +CONVERT(VARCHAR(20), @.BalconyDeckPatio)IF @.UseOfYardISNOT NULLSET @.SQL = @.SQL +' AND a.UseOfYard = ' +CONVERT(VARCHAR(20), @.UseOfYard)IF @.DishwasherISNOT NULLSET @.SQL = @.SQL +' AND a.Dishwasher = ' +CONVERT(VARCHAR(20), @.Dishwasher)IF @.WasherDryerISNOT NULLSET @.SQL = @.SQL +' AND a.WasherDryer = ' +CONVERT(VARCHAR(20), @.WasherDryer)IF @.FireplaceISNOT NULLSET @.SQL = @.SQL +' AND a.Fireplace = ' +CONVERT(VARCHAR(20), @.Fireplace)IF @.EIKISNOT NULLSET @.SQL = @.SQL +' AND a.EIK = ' +CONVERT(VARCHAR(20), @.EIK)IF @.HardwoodFloorsISNOT NULLSET @.SQL = @.SQL +' AND a.HardwoodFloors = ' +CONVERT(VARCHAR(20), @.HardwoodFloors)IF @.BroadBandNetISNOT NULLSET @.SQL = @.SQL +' AND a.BroadbandNet = ' +CONVERT(VARCHAR(20), @.BroadbandNet)IF @.TVISNOT NULLSET @.SQL = @.SQL +' AND a.TV = ' +CONVERT(VARCHAR(20), @.TV)IF @.ThermostatISNOT NULLSET @.SQL = @.SQL +' AND a.Thermostat = ' +CONVERT(VARCHAR(20), @.Thermostat)IF @.LandlordNotPresentISNOT NULLSET @.SQL = @.SQL +' AND a.LandLordNotPresent = ' +CONVERT(VARCHAR(20), @.LandLordNotPresent)IF @.SmokingISNOT NULLSET @.SQL = @.SQL +' AND a.Smoking = ' +CONVERT(VARCHAR(20), @.Smoking)IF @.NoPetsAllowedISNOT NULLSET @.SQL = @.SQL +' AND a.NoPetsAllowed = ' +CONVERT(VARCHAR(20), @.NoPetsAllowed)IF @.CatISNOT NULLSET @.SQL = @.SQL +' AND a.Cat = ' +CONVERT(VARCHAR(20), @.Cat)IF @.MoreCatsISNOT NULLSET @.SQL = @.SQL +' AND a.MoreCats = ' +CONVERT(VARCHAR(20), @.MoreCats)IF @.SmallDogISNOT NULLSET @.SQL = @.SQL +' AND a.SmallDog = ' +CONVERT(VARCHAR(20), @.SmallDog)IF @.LargeDogsISNOT NULLSET @.SQL = @.SQL +' AND a.LargeDogs = ' +CONVERT(VARCHAR(20), @.LargeDogs)IF @.DoorpersonISNOT NULLSET @.SQL = @.SQL +' AND a.Doorperson = ' +CONVERT(VARCHAR(20), @.Doorperson)IF @.IngroundPoolISNOT NULLSET @.SQL = @.SQL +' AND a.IngroundPool = ' +CONVERT(VARCHAR(20), @.IngroundPool)IF @.AboveGroundPoolISNOT NULLSET @.SQL = @.SQL +' AND a.AboveGroundPool = ' +CONVERT(VARCHAR(20), @.AboveGroundPool)IF @.ElevatorISNOT NULLSET @.SQL = @.SQL +' AND a.Elevator = ' +CONVERT(VARCHAR(20), @.Elevator)IF @.UseOfGarageISNOT NULLSET @.SQL = @.SQL +' AND a.UseOfGarage = ' +CONVERT(VARCHAR(20), @.UseOfGarage)IF @.LaundryFacilitiesISNOT NULLSET @.SQL = @.SQL +' AND a.LaundryFacilities = ' +CONVERT(VARCHAR(20), @.LaundryFacilities)IF @.HealthCenterISNOT NULLSET @.SQL = @.SQL +' AND a.Health Center = ' +CONVERT(VARCHAR(20), @.HealthCenter)IF @.StorageAreasISNOT NULLSET @.SQL = @.SQL +' AND a.StorageAreas = ' +CONVERT(VARCHAR(20), @.StorageAreas)IF @.WheelchairAccessISNOT NULLSET @.SQL = @.SQL +' AND a.WheelchairAccess = ' +CONVERT(VARCHAR(20), @.WheelchairAccess)IF @.BusinessCentersISNOT NULLSET @.SQL = @.SQL +' AND a.BusinessCenters = ' +CONVERT(VARCHAR(20), @.BusinessCenters)IF @.RentChargeMinISNOT NULL AND @.RentChargeMAXISNOT NULLSET @.SQL = @.SQL +' AND a.RentCharge BETWEEN ' +CONVERT(VARCHAR(20), @.RentChargeMin) +' AND ' +CONVERT(VARCHAR(20), @.RentChargeMax)IF @.RentChargeMinISNOT NULL AND @.RentChargeMAXISNULLSET @.SQL = @.SQL +' AND a.RentCharge >= ' +CONVERT(VARCHAR(20), @.RentChargeMin)IF @.RentChargeMAXISNULL AND @.RentChargeMAXISNOT NULLSET @.SQL = @.SQL +' AND a.RentCharge <= ' +CONVERT(VARCHAR(20), @.RentChargeMax)IF @.Debug = 1PRINT @.SQLEXEC (@.SQL)

Try this:

@.StudioINT =NULL,
@.Br1INT =NULL,
@.Br2INT =NULL,
@.Br3INT =NULL,
@.Br4INT =NULL,
@.OverBr4INT =NULL,
@.CondoINT =NULL,
@.ListingTypevarchar(10) =NULL,
@.WindowAirINT =NULL,
@.CentralACINT =NULL,
@.BalconyDeckPatioINT =NULL,
@.UseOfYardINT =NULL,
@.DishwasherINT =NULL,
@.WasherDryerINT =NULL,
@.FireplaceINT =NULL,
@.EIKINT =NULL,
@.HardwoodFloorsINT =NULL,
@.BroadbandNetINT =NULL,
@.TVINT =NULL,
@.ThermostatINT =NULL,
@.LandlordNotPresentINT =NULL,
@.SmokingINT =NULL,
@.NoPetsAllowedINT =NULL,
@.CatINT =NULL,
@.MoreCatsINT =NULL,
@.SmallDogINT =NULL,
@.LargeDogsINT =NULL,
@.DoorpersonINT =NULL,
@.IngroundPoolINT =NULL,
@.AboveGroundPoolINT =NULL,
@.ElevatorINT =NULL,
@.UseOfGarageINT =NULL,
@.LaundryFacilitiesINT =NULL,
@.HealthCenterINT =NULL,
@.StorageAreasINT =NULL,
@.WheelchairAccessINT =NULL,
@.BusinessCentersINT =NULL,
@.RentChargeMinINT =NULL,
@.RentChargeMaxINT =NULL,
@.DebugBIT = 1
)
AS

SET NOCOUNT ON

SELECT

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 (@.StudioIS NULL OR r.StudioFlag =@.Studio)
AND (@.Br1IS NULL OR r.BRFlag1 = @.Br1)
...
AND (@.ListingType IS NULL OR r.ListingType =@.ListingType)
...
etc...

|||

but the IF's are there to match conditional options... how would (@.ListingType IS NULL OR r.ListingType =@.ListingType) make that work still?

|||

Nevermind... the variable needed to have single quotes around it..

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

|||

Mnemonic:

but the IF's are there to match conditional options... how would (@.ListingType IS NULL OR r.ListingType =@.ListingType) make that work still?

If ListingType equals null then

(@.ListingType IS NULL OR r.ListingType =@.ListingType) is also true! This is just another way of handling optional parameters.

So yes, the missing quotes are causing your error, but the solution that I wrote will also work (did you try it?) and I believe it is the better solution. Another advantage of using parameters is that you don't have to worry about wheter or not to use quotes...

|||

Oh... reading the logic you're definitly right...

I went my way with the query, but i might switch it over to yours... because the select statement is inside of @.sql (i think) none of my schema is showing any columns on my gridview...

Any idea on why that would do that? I would really rather not re-write this query (for the 3rd time)