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.
| Author |
Topic |
|
Mnemonic
Yak Posting Veteran
58 Posts |
 Posted - 2007-11-14 : 18:08:11
|
| Hey guys, I have two stored procedures.. the first one gets optional values returned by the table, but i also want the zip code search option (2nd SP on here) to be an optional choice for the first query... how would i put them together?1st query:( @Studio INT = NULL, @Br1 INT = NULL, @Br2 INT = NULL, @Br3 INT = NULL, @Br4 INT = NULL, @OverBr4 INT = NULL, @Condo INT = NULL, @ListingType varchar(10) = 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 ONDECLARE @SQL VARCHAR(8000)SET @SQL = '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.RentFrequencyFROM db_REListings as rLEFT JOIN db_RentalAmenities AS a ON a.REListingID = r.REListingIDWHERE 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 NULLSET @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 @SQLEXEC (@SQL)2nd Query: ( @ZipCode Numeric(5, 0) = Null, @Miles Float )As set nocount on Declare @Latitude Float(10) Declare @Longitude Float(10) -- Lookup longitude, latitude for zip codes Select @Latitude = Latitude, @Longitude = Longitude From db_ZipCodes Where ZIP_CODE = @ZipCode SELECT a.REListingID , a.REListingDate , a.RESubject , b.Thumb, Round(Avg(3958.75 * ACos(Sin(@Latitude/57.2958) * Sin(Latitude/57.2958) + Cos(@Latitude/57.2958) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - @Longitude/57.2958))),2) As MilesFROM db_Zipcodes INNER JOIN db_REListings a ON db_Zipcodes.ZIP_CODE = a.ZipCode inner JOIN ( Select Row_Number() over (Partition by aa.ListingID order by aa.thumb) as RowID,aa.* from db_REPics aa ) b ON a.ListingID = b.REListingID Where b.RowID = 1and Longitude Is Not Null And Latitude Is Not Null And @Miles >= ( 3958.75 * ACos(Sin(@Latitude/57.2958) * Sin(Latitude/57.2958) + Cos(@Latitude/57.2958) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - @Longitude/57.2958)) )Group by db_Zipcodes.ZIP_CODE, db_Zipcodes.CITY, a.ZipCode, a.REListingID, a.REListingDate, a.RESubject, b.ThumbOrder by milesreturn |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 18:24:07
|
Do you mean you want all of the 2nd query to be potentially added to the 1st? or as a 2nd result set when the option is chosen?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-14 : 18:29:19
|
| I want the 2nd query to be added to the first :) |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 18:56:00
|
Hmmmm.Best guess:(removed for thread sanity as later posts replaced this code This is up there with a million monkeys in a room with typewriters...but I think it might be close ...If the 2nd query params are null (as passed to the proc), it does 1st query as is. Otherwise, it does the kind of merged query combining amenities based query with the zip code miles order search...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-14 : 22:57:21
|
| Wow, thats impressive man! However, I got a buttload of errors saying:Msg 137, Level 15, State 2, Procedure sp_REListings_SelectAmenities, Line 358Must declare the scalar variable "@SQL". |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 23:03:17
|
In your procedure, before the first IF statement, DECLARE @SQL varchar(8000)You got an error for every line that @SQL appeared...see what happens if you declare it, I will be up for a while to see what happens.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-14 : 23:09:32
|
| That worked, and it works fine for the query without the zip code.. but now i'm getting this when i try to do a zip search:Msg 8114, Level 16, State 5, Procedure sp_REListings_SelectAmenities, Line 202Error converting data type varchar to real. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 23:17:53
|
I think I found it. I forgot to unwrap one of the @Latitude variables from Query2...removed for thread sanity after replacement code posted later in the thread
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 23:23:03
|
Question for clarity and curiousity ..(3958.75 * ACos(Sin(@Latitude/57.2958) *Sin(Latitude/57.2958) +Cos(@Latitude/57.2958) *Cos(Latitude/57.2958) *Cos(Longitude/57.2958 - @Longitude/57.2958))) removed for thread sanity in favor of later code posted
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-14 : 23:27:49
|
| Still same error... i realized i messed with the join's in the 2nd query.. i didnt have the image table for this one, so i put:FROM db_Zipcodes INNER JOINdb_REListings c ON db_Zipcodes.ZIP_CODE = c.ZipCodeINNER JOIN db_RentalAmenities a ON c.REListingID = a.REListingIDDoes this look right? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 23:33:04
|
Yes, that looks correct. I may have inverted or forgot to alias them correctly in mine.Note in my very last post I bolded a necessary filter before all of the stuff peso wrote for you to filter for @ZIPCODE.If you have IM, I am on AOL and yahoo as same screenname
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-14 : 23:42:21
|
| I just tried IM'ing you, says you were offline on AIM. Anyways, here's my current SP, and below it are the errors im getting:(@Studio INT = NULL,@Br1 INT = NULL,@Br2 INT = NULL,@Br3 INT = NULL,@Br4 INT = NULL,@OverBr4 INT = NULL,@Condo INT = NULL,@ListingType varchar(10) = 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,@ZipCode Numeric(5, 0) = Null,@Miles Float = NULL,@Debug BIT = 1)AS DECLARE @SQL varchar(8000)----FIRST QUERY if ZIP CODE IS NULL and MILES is nullIF (@ZipCode is null) or (@Miles is null)BEGIN SET @SQL = ' 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 LEFT 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) ENDIF @ZipCode is not null and @Miles is not nullBEGIN SET @SQL = ' SELECT r.REListingID, r.REListingDate, b.Thumb, 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 , Round(Avg(3958.75 * ACos(Sin(Latitude/57.2958 ) * Sin(Latitude/57.2958) + Cos(Latitude/57.2958 ) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - Longitude/57.2958))),2) As MilesFROM db_Zipcodes INNER JOINdb_REListings c ON db_Zipcodes.ZIP_CODE = c.ZipCodeINNER JOIN db_RentalAmenities a ON c.REListingID = a.REListingID WHERE 1 = 1 and db_Zipcodes.ZIP_CODE = ' + @ZipCode + ' 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)END IF @Debug = 1--- GO TIME PRINT @SQL EXEC (@SQL)Errors:Msg 156, Level 15, State 1, Procedure sp_REListings_SelectAmenities, Line 254Incorrect syntax near the keyword 'AND'.Msg 105, Level 15, State 1, Procedure sp_REListings_SelectAmenities, Line 333Unclosed quotation mark after the character string ' + CONVERT(VARCHAR(20), @RentChargeMax)END IF @Debug = 1--- GO TIME PRINT @SQL EXEC (@SQL)'. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 23:42:53
|
Okay...I needed to clean this up a little bit for visual purposes.What I did was declared the dynamic condition clauses ONCE at the begining. The result clause is then appended to the end of either option 1 or 2. This "looks" cleaner to me, but should produce the same results (error?) LOL. Also not reposting the variable declaration piece before ASsee next post
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-14 : 23:53:54
|
Actually I put what you posted in my QA and there was actually a missing ' after the @ZIPCODE filterremoved after changes made to resulting code to simplify thread reading That code might do it..
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-15 : 17:36:04
|
| It seems like its damn close... with Declare @ENDSQL = '' i am getting an error:Msg 102, Level 15, State 1, Procedure sp_REListings_SelectAmenities, Line 52Incorrect syntax near '='.Sorry i wasnt on aim last night... i got frustrated and decided to get drunk. haha |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-15 : 18:49:41
|
I probably WAS drunk.Anyway, that line should be SET or SELECT instead of DECLARE (it is already declared above.)I am online tonight, feel free to shout out.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-15 : 18:58:01
|
| Welll, it saved! But this is what i get when running a Zip search now:Msg 8115, Level 16, State 6, Procedure sp_REListings_SelectAmenities, Line 169Arithmetic overflow error converting varchar to data type numeric. (1 row(s) affected)Here's my full code at this point :(@Studio INT = NULL,@Br1 INT = NULL,@Br2 INT = NULL,@Br3 INT = NULL,@Br4 INT = NULL,@OverBr4 INT = NULL,@Condo INT = NULL,@ListingType varchar(10) = 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,@ZipCode Numeric(5, 0) = Null,@Miles Float = NULL,@Debug BIT = 1)AS DECLARE @SQL varchar(8000), @ENDSQL varchar(8000)set @ENDSQL = ''----BUILD DYNAMIC CONDITIONAL SQL HERE IF @Studio IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio) IF @Br1 IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1) IF @Br2 IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2) IF @Br3 IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3) IF @Br4 IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4) IF @OverBr4 IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4) IF @Condo IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo) IF @ListingType IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND r.ListingType = ''' + CONVERT(VARCHAR(20), @ListingType) + '''' IF @WindowAir IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir) IF @CentralAC IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC) IF @BalconyDeckPatio IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio) IF @UseOfYard IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard) IF @Dishwasher IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher) IF @WasherDryer IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer) IF @Fireplace IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace) IF @EIK IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK) IF @HardwoodFloors IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors) IF @BroadBandNet IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet) IF @TV IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV) IF @Thermostat IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat) IF @LandlordNotPresent IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent) IF @Smoking IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking) IF @NoPetsAllowed IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed) IF @Cat IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat) IF @MoreCats IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats) IF @SmallDog IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog) IF @LargeDogs IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs) IF @Doorperson IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson) IF @IngroundPool IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool) IF @AboveGroundPool IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool) IF @Elevator IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator) IF @UseOfGarage IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage) IF @LaundryFacilities IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities) IF @HealthCenter IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter) IF @StorageAreas IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas) IF @WheelchairAccess IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess) IF @BusinessCenters IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters) IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax) IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL SET @ENDSQL = @ENDSQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin) IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL SET @ENDSQL = @ENDSQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)----FIRST QUERY if ZIP CODE IS NULL and MILES is nullIF (@ZipCode is null) or (@Miles is null)BEGIN SET @SQL = ' 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 LEFT JOIN db_RentalAmenities AS a ON a.REListingID = r.REListingID WHERE 1 = 1 ' + @ENDSQL ENDIF @ZipCode is not null and @Miles is not null BEGIN SET @SQL = ' 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, Round(Avg(3958.75 * ACos(Sin(Latitude/57.2958 ) * Sin(Latitude/57.2958) + Cos(Latitude/57.2958 ) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - Longitude/57.2958))),2) As MilesFROM db_Zipcodes INNER JOINdb_REListings c ON db_Zipcodes.ZIP_CODE = c.ZipCodeINNER JOIN db_RentalAmenities a ON c.REListingID = a.REListingIDWHERE 1 = 1 and db_Zipcodes.ZIP_CODE = ' + @ZipCode + @ENDSQLEND IF @Debug = 1--- GO TIME PRINT @SQL EXEC (@SQL) |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-15 : 19:20:50
|
What is the datatype of your ZIP_CODE field in the Zipcodes table?is it the same datatype as ZipCode in RE_Listings table?If one is number and one is text, the one with text has to be scrubbed to have clean numbers. If one is numbers, the query or join for that needs to be modified slightly.Since the query is dynamic, QA cannot truly compile it, so there will be some troubleshooting.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-15 : 19:25:27
|
| Ahh, i think you got it... in Zipcodes table it is char(5), and in REListings table it is int. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-15 : 19:27:39
|
change this part in near the bottom of part 2ON db_Zipcodes.ZIP_CODE = cast(c.ZipCode as varchar)
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2007-11-15 : 19:30:52
|
| still the same error :( |
|
|
|
Next Page
|
|
|
|
|
Combining two stored procedures