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)
 I need a little help with this loooong query..

Author  Topic 

Mnemonic
Yak Posting Veteran

58 Posts
Posted - 2007-11-08 : 19:37:01
I wrote this query to get optional preferences in the users search, in my application written in asp .net.

The problem i'm getting is probably inside my BETWEEN syntax... Can anyone help?


@studio int = null,
@br1 int = null,
@br2 int = null,
@br3 int = null,
@br4 int = null,
@overbr4 int = null,
@condo int = null,
@state_id int = null,
@city_id 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

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)

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

IF @studio IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.StudioFlag = @studio'
IF @br1 IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.BRFlag1 = @br1'
IF @br2 IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.BRFlag2 = @br2'
IF @br3 IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.BRFlag3 = @br3'
IF @br4 IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.BRFlag4 = @br4'
IF @overbr4 IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.OverBRFlag4 = @overbr4'
IF @condo IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.CondoFlag = @condo'
IF @state_id IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.State_ID = @state_id'
IF @city_id IS NOT NULL
SELECT @sql = @sql + ' AND db_REListings.city_id = @city_id'
IF @WindowAir IS NOT NULL
SELECT @sql = @sql + ' AND db_RentalAmenities.WindowAir = @windowair'
IF @CentralAC IS NOT NULL
SELECT @sql = @sql + ' AND db_RentalAmenities.CentralAir = @CentralAC'


IF @BalconyDeckPatio is not null
Select @sql = @sql + ' AND db_RentalAmenities.BalconyDeckPatio = @BalconyDeckPatio'
IF @UseOfYard is not null
Select @sql = @sql + ' AND db_RentalAmenities.UseOfYard = @UseOfYard'
IF @Dishwasher is not null
Select @sql = @sql + ' AND db_RentalAmenities.Dishwasher = @Dishwasher'
IF @WasherDryer is not null
Select @sql = @sql + ' AND db_RentalAmenities.WasherDryer = @WasherDryer'
IF @Fireplace is not null
Select @sql = @sql + ' AND db_RentalAmenities.Fireplace = @Fireplace'
IF @EIK is not null
Select @sql = @sql + ' AND db_RentalAmenities.EIK = @EIK'
IF @HardwoodFloors is not null
Select @sql = @sql + ' AND db_RentalAmenities.HardwoodFloors = @HardwoodFloors'
IF @BroadBandNet is not null
Select @sql = @sql + ' AND db_RentalAmenities.BroadbandNet = @BroadbandNet'
IF @TV is not null
Select @sql = @sql + ' AND db_RentalAmenities.TV = @TV'
IF @Thermostat is not null
Select @sql = @sql + ' AND db_RentalAmenities.Thermostat = @Thermostat'
IF @LandlordNotPresent is not null
Select @sql = @sql + ' AND db_RentalAmenities.LandLordNotPresent = @LandLordNotPresent'
IF @Smoking is not null
Select @sql = @sql + ' AND db_RentalAmenities.Smoking = @Smoking'


IF @NoPetsAllowed is not null
Select @sql = @sql + ' AND db_RentalAmenities.NoPetsAllowed = @NoPetsAllowed'
IF @Cat is not null
Select @sql = @sql + ' AND db_RentalAmenities.Cat = @Cat'
IF @MoreCats is not null
Select @sql = @sql + ' AND db_RentalAmenities.MoreCats = @MoreCats'
IF @SmallDog is not null
Select @sql = @sql + ' AND db_RentalAmenities.SmallDog = @SmallDog'
IF @LargeDogs is not null
Select @sql = @sql + ' AND db_RentalAmenities.LargeDogs = @LargeDogs'

IF @Doorperson is not null
Select @sql = @sql + ' AND db_RentalAmenities.Doorperson = @Doorperson'
IF @IngroundPool is not null
Select @sql = @sql + ' AND db_RentalAmenities.IngroundPool = @IngroundPool'
IF @AboveGroundPool is not null
Select @sql = @sql + ' AND db_RentalAmenities.AboveGroundPool = @AboveGroundPool'
IF @Elevator is not null
Select @sql = @sql + ' AND db_RentalAmenities.Elevator = @Elevator'
IF @UseOfGarage is not null
Select @sql = @sql + ' AND db_RentalAmenities.UseOfGarage = @UseOfGarage'
IF @LaundryFacilities is not null
Select @sql = @sql + ' AND db_RentalAmenities.LaundryFacilities = @LaundryFacilities'
IF @HealthCenter is not null
Select @sql = @sql + ' AND db_RentalAmenities.Health Center = @HealthCenter'
IF @StorageAreas is not null
Select @sql = @sql + ' AND db_RentalAmenities.StorageAreas = @StorageAreas'
IF @WheelchairAccess is not null
Select @sql = @sql + ' AND db_RentalAmenities.WheelchairAccess = @WheelchairAccess'
IF @BusinessCenters is not null
Select @sql = @sql + ' AND db_RentalAmenities.BusinessCenters = @BusinessCenters'
IF @RentChargeMin + @RentChargeMAX is not null
Select @sql = @sql + ' AND db_RentalAmenities.RentCharge Between @RentchargeMin AND @RentChargeMax'


IF @debug = 1
PRINT @sql

SELECT @paramlist = '@studio int,
@br1 int,
@br2 int,
@br3 int,
@br4 int,
@overbr4 int,
@condo int,
@state_id int,
@city_id int,
@WindowAir int,
@CentralAC int,
@BalconyDeckPatio int,
@UseOfYard int,
@Dishwasher int,
@WasherDryer int,
@Fireplace int,
@EIK int,
@HardwoodFloors int,
@BroadbandNet int,
@TV int,
@Thermostat int,
@LandlordNotPresent int,
@Smoking int,
@NoPetsAllowed int,
@Cat int,
@MoreCats int,
@SmallDog int,
@LargeDogs int,
@Doorperson int,
@IngroundPool int,
@AboveGroundPool int,
@Elevator int,
@UseOfGarage int,
@LaundryFacilities int,
@HealthCenter int,
@StorageAreas int,
@WheelchairAccess int,
@BusinessCenters int,
@RentchargeMin int,
@RentchargeMax int,
'

EXEC sp_executesql @sql, @paramlist,
@br1,
@br2,
@br3,
@br4,
@overbr4,
@condo,
@state_id,
@city_id,
@WindowAir,
@CentralAC,
@BalconyDeckPatio,
@UseOfYard,
@Dishwasher,
@WasherDryer,
@Fireplace,
@EIK,
@HardwoodFloors,
@BroadbandNet,
@TV,
@Thermostat,
@LandlordNotPresent,
@Smoking,
@NoPetsAllowed,
@Cat,
@MoreCats,
@SmallDog,
@LargeDogs,
@Doorperson,
@IngroundPool,
@AboveGroundPool,
@Elevator,
@UseOfGarage,
@LaundryFacilities,
@HealthCenter,
@StorageAreas,
@WheelchairAccess,
@BusinessCenters,
@RentChargeMin,
@RentChargeMax

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-11-08 : 19:40:42
What is the error msg? Please also post some values for the parameters that you are using..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Mnemonic
Yak Posting Veteran

58 Posts
Posted - 2007-11-08 : 19:45:44
This is my values:

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_REListings_SelectAmenities]
@RentChargeMin = 100,
@RentChargeMax = 1000

SELECT 'Return Value' = @return_value

GO


And this is the error:


SELECT db_REListings.REListingID, db_REListings.REListingDate, db_REListings.Username, db_REListings.State_ID, db_REListings.City_ID,
db_REListings.ZipCode, db_REListings.ListingType, db_REListings.StudioFlag, db_REListings.BRFlag1, db_REListings.BRFlag2,
db_REListings.BRFlag3, db_REListings.BRFlag4, db_REListings.OverBRFlag4, db_REListings.CondoFlag, db_RentalAmenities.WindowAir,
db_RentalAmenities.CentralAir, db_RentalAmenities.BalconyDeckPatio, db_RentalAmenities.UseOfYard, db_RentalAmenities.Dishwasher,
db_RentalAmenities.WasherDryer, db_RentalAmenities.Fireplace, db_RentalAmenities.EIK, db_RentalAmenities.HardwoodFloors,
db_RentalAmenities.BroadbandNet, db_RentalAmenities.TV, db_RentalAmenities.Thermostat, db_RentalAmenities.LandlordNotPresent,
db_RentalAmenities.Smoking, db_RentalAmenities.NoPetsAllowed, db_RentalAmenities.Cat, db_RentalAmenities.MoreCats,
db_RentalAmenities.SmallDog, db_RentalAmenities.LargeDogs, db_RentalAmenities.Doorperson, db_RentalAmenities.IngroundPool,
db_RentalAmenities.AboveGroundPool, db_RentalAmenities.Elevator, db_RentalAmenities.UseOfGarage, db_RentalAmenities.LaundryFacilities,
db_RentalAmenities.HealthCenter, db_RentalAmenities.StorageAreas, db_RentalAmenities.WheelchairAccess, db_RentalAmenities.BusinessCenters,
db_RentalAmenities.RentCharge, db_RentalAmenities.RentFrequency
FROM db_REListings INNER JOIN
db_RentalAmenities ON db_REListings.REListingID = db_RentalAmenities.REListingID
WHERE 1 = 1 AND db_RentalAmenities.RentCharge Between @RentchargeMin AND @RentChargeMax
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ')'.

(1 row(s) affected)
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-08 : 20:25:02
I don't see a ) anywhere in your posted code. Is there a ) in any of your non-posted procedure, or in one of the variables? If you double click the error, does it take you to line 41 where might see the offending issue?
Go to Top of Page

Mnemonic
Yak Posting Veteran

58 Posts
Posted - 2007-11-08 : 20:51:17
Seems i had an extra "," in the paramlist. Now i am getting this error:

SELECT db_REListings.REListingID, db_REListings.REListingDate, db_REListings.Username, db_REListings.State_ID, db_REListings.City_ID,
db_REListings.ZipCode, db_REListings.ListingType, db_REListings.StudioFlag, db_REListings.BRFlag1, db_REListings.BRFlag2,
db_REListings.BRFlag3, db_REListings.BRFlag4, db_REListings.OverBRFlag4, db_REListings.CondoFlag, db_RentalAmenities.WindowAir,
db_RentalAmenities.CentralAir, db_RentalAmenities.BalconyDeckPatio, db_RentalAmenities.UseOfYard, db_RentalAmenities.Dishwasher,
db_RentalAmenities.WasherDryer, db_RentalAmenities.Fireplace, db_RentalAmenities.EIK, db_RentalAmenities.HardwoodFloors,
db_RentalAmenities.BroadbandNet, db_RentalAmenities.TV, db_RentalAmenities.Thermostat, db_RentalAmenities.LandlordNotPresent,
db_RentalAmenities.Smoking, db_RentalAmenities.NoPetsAllowed, db_RentalAmenities.Cat, db_RentalAmenities.MoreCats,
db_RentalAmenities.SmallDog, db_RentalAmenities.LargeDogs, db_RentalAmenities.Doorperson, db_RentalAmenities.IngroundPool,
db_RentalAmenities.AboveGroundPool, db_RentalAmenities.Elevator, db_RentalAmenities.UseOfGarage, db_RentalAmenities.LaundryFacilities,
db_RentalAmenities.HealthCenter, db_RentalAmenities.StorageAreas, db_RentalAmenities.WheelchairAccess, db_RentalAmenities.BusinessCenters,
db_RentalAmenities.RentCharge, db_RentalAmenities.RentFrequency
FROM db_REListings INNER JOIN
db_RentalAmenities ON db_REListings.REListingID = db_RentalAmenities.REListingID
WHERE 1 = 1 AND db_RentalAmenities.RentCharge Between @RentchargeMin AND @RentChargeMax
Msg 8178, Level 16, State 1, Line 0
Parameterized Query '(@studio int,
@br1 int,
@br2 int,
@br3 int,
@br4 int,
@over' expects parameter @RentchargeMax, which was not supplied.

(1 row(s) affected)
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-09 : 17:05:34
That error looks like pretty specific, 1 parameter was not supplied.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-11-09 : 17:31:13
I enhanced the RentCharge for you too...
CREATE PROCEDURE dbo.uspMySearch
(
	@Studio			INT = NULL,
	@Br1			INT = NULL,
	@Br2			INT = NULL,
	@Br3			INT = NULL,
	@Br4			INT = NULL,
	@OverBr4		INT = NULL,
	@Condo			INT = NULL,
	@State_ID		INT = NULL,
	@City_ID		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.REListingID,
		r.REListingDate,
		r.Username,
		r.State_ID,
		r.City_ID,
		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 @State_ID IS NOT NULL
	SET @SQL = @SQL + ' AND r.State_ID = ' + CONVERT(VARCHAR(20), @State_ID)
IF @City_ID IS NOT NULL
	SET @SQL = @SQL + ' AND r.city_id = ' + CONVERT(VARCHAR(20), @City_ID)
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)


EDIT: Removed double AND for last line
E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Mnemonic
Yak Posting Veteran

58 Posts
Posted - 2007-11-09 : 18:04:15
Peso, thank you so much, but when i try to exec that it says :

Msg 156, Level 15, State 1, Procedure sp_REListings_SelectAmenities, Line 183
Incorrect syntax near the keyword 'AND'.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-09 : 18:16:10
IF @RentChargeMAX IS NULL AND AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)

See the two "AND" next to each other. I am guessing that is at line 183. Funny thing, if you double click that error message in QA it takes you right to the stop (at least it does on mine).

Remove the extra "AND"
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-11-09 : 18:19:05
The AND is repeated twice.. if you spent a few minutes trying to figure out rather than immediatly cut/paste the error here and wait for someone to feed you, you'd have it figured out. sorry to be rude but syntax errors are most basic stuff you should be able to figure out yourself. SQL Server is even telling you the line number..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Mnemonic
Yak Posting Veteran

58 Posts
Posted - 2007-11-09 : 19:35:43
Question... Why did you convert the int's to varchar? The values in those fields are acctually 0's or 1's.
Go to Top of Page

Mnemonic
Yak Posting Veteran

58 Posts
Posted - 2007-11-09 : 20:06:53
This query works fine inside of SQL Server Managment studio, but when i run the code in ASP .net i get this error:

Must declare the scalar variable "@WindowAir".

Any ideas? I'm sorry if things look obvious to you and not to me.. i'm new to t-sql.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-09 : 20:58:17


He is converting the values to varchar so they can be included in the string result properly.

string + value = error
string + string is okay.

strange about the variable declaration, since it is declare as part of the parameters at the beginning. I am not too familiar with asp.net syntax differences yet...
Go to Top of Page

Mnemonic
Yak Posting Veteran

58 Posts
Posted - 2007-11-09 : 21:23:03
OK, everything works fine.. again, i'm really sorry if im asking too much.

I have another t-sql query that returns rows based on zip code. I wanted to make this work within this t-sql query, so if a user DECIDES to search for something with a above ground pool and a window air conditioner within 50 miles of their zip, it would be handled on one query. here is my zip query that returns just based on the subject line:

(
@ZipCode Numeric(5, 0) = Null,
@Miles Float,
@subject varchar(255)
)
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.ListingDate
, a.ListingSubject
, a.PriceAsked
, a.Catagory_ID
, a.SubCatagory_ID
, a.State_ID
, a.City_ID
, 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 Miles
FROM db_Zipcodes INNER JOIN
db_REListings a ON db_Zipcodes.ZIP_CODE = a.ZipCode inner JOIN
(
Select Row_Number() over (Partition by aa.REListingID order by aa.thumb) as RowID,aa.*
from db_REPics aa
) b ON a.REListingID = b.REListingID



Where b.RowID = 1
and
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))
)
and ListingSubject LIKE '%' + @subject + '%'


--And ZIP_CODE != @ZipCode
Group by db_Zipcodes.ZIP_CODE, db_Zipcodes.CITY, a.ZipCode, a.ListingID, a.Catagory_ID, a.SubCatagory_ID, a.ListingDate, a.ListingSubject, a.PriceAsked, a.State_ID, a.City_ID,b.Thumb
Order by miles
return
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site