Hard Sql Query - 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)

Hard Sql Query

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 03:10:48

I am trying to build a SQL Query (Stored Procedure) but am very stuck. My original SP looks like this.

ALTER PROCEDURE dbo.bt_BizForSale_Search
(
@ID int,
@AgencyName nvarchar(50),
@Contact nvarchar(50),
@ContactPhone nvarchar(50),
@BusinessArea nvarchar(50),
@Address nvarchar(50),
@City nvarchar(50),
@Province nvarchar(50),
@PostalCode nvarchar(50),
@ContactEmailAddress nvarchar(50),
@TagLine nvarchar(50),
@WebSite nvarchar(50),
@BusinessExtension nvarchar(50),
@Classification nvarchar(50),
@SaleStructure nvarchar(50),
@SellingPriceHigh Money,
@SellingPriceLow Money,
@EBITDALow money,
@EBITDAHigh money,
@AnnualLow money,
@AnnualHigh money,
@DownLow money,
@DownHigh money,
@TakeBack bit
)

AS
SET NOCOUNT ON
SET ANSI_NULLS ON

SELECT *

FROM dbo.bt_BizForSale

Where
(
(ID = COALESCE(@ID, ID))
And ([TakeBack] = COALESCE(@TakeBack, [TakeBack]))
And (@TagLine Is Null Or [TagLine] Like '%' + @TagLine + '%')
And (@BusinessExtension Is Null Or [BusinessExtension] Like '%' + @BusinessExtension + '%')
And (@BusinessArea Is Null Or [BusArea] Like '%' + @BusinessArea + '%')
And (@Classification Is Null Or [PrimaryClassification] Like '%' + @Classification + '%' Or [SecondaryClassification] Like '%' + @Classification + '%')
And (@AgencyName Is Null Or [AgencyName] Like '%' + @AgencyName + '%')
And (@Contact Is Null Or [PrimaryContact] Like '%' + @Contact + '%' Or [SecondaryContact] Like '%' + @Contact + '%')
And (@ContactPhone Is Null Or [PrimaryContactPhone] Like '%' + @ContactPhone + '%' Or [SecondaryContactPhone] Like '%' + @ContactPhone + '%')
And (@ContactEmailAddress Is Null Or [ContactEmailAddress] Like '%' + @ContactEmailAddress + '%')
And (@WebSite Is Null Or [WebSite] Like '%' + @WebSite + '%')
And (@Address Is Null Or [Address] Like '%' + @Address + '%')
And (@City Is Null Or [City] Like '%' + @City + '%' Or [BusCity] Like '%' + @City + '%')
And (@Province Is Null Or [Province] Like '%' + @Province + '%' Or [BusProvince] Like '%' + @Province + '%')
And (@PostalCode Is Null Or [PostalCode] Like '%' + @PostalCode + '%')
And (@SaleStructure Is Null Or [SaleStructure] Like '%' + @SaleStructure + '%')
And (@SellingPriceLow Is Null Or @SellingPriceHigh Is Null Or [SellingPrice] Between @SellingPriceLow And @SellingPriceHigh)
And (@EBITDALow Is Null Or @EBITDAHigh Is Null Or [SusEarn] Between @EBITDALow And @EBITDAHigh)
And (@AnnualLow Is Null Or @AnnualHigh Is Null Or [SusRev] Between @AnnualLow And @AnnualHigh)
And (@DownLow Is Null Or @DownHigh Is Null Or [DownPayment] Between @DownLow And @DownHigh)
And ([isEnabled] = 1)
And ([DateExpires] > GetDate())
)

Order By [BusinessName]

But now My client is adding multiple "Classifications". Basically the old way was
PrimaryClassification
SecondaryClassification
and the above query works fine... but now he wants

PrimaryClassificationA
SecondaryClassificationA1
SecondaryClassificationA2
SecondaryClassificationA3

PrimaryClassificationB
SecondaryClassificationB1
SecondaryClassificationB2
SecondaryClassificationB3

PrimaryClassificationC
SecondaryClassificationC1
SecondaryClassificationC2
SecondaryClassificationC3

and only the PrimaryClassificationA and SecondaryClassificationA1 are "Required".

Does anyone know how I would go about creating a query like this?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 03:12:34

will SecondaryClassificationA1,SecondaryClassificationA2
,.. will be seperate fields in table?

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 03:17:53

Thank you for replying, they are seperate fields.

pls look at this :http://files.infinitas.ws/images/forums/aspnet-2690918.png

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 03:41:44

then just extend your procedure to look for each of SecondaryClassification fields in filter instead of single field now.

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 10:48:24

Ok, but a "Filter" is a new concept to me. Have you any direction?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 10:56:43

quote:
Originally posted by rammohan

Ok, but a "Filter" is a new concept to me. Have you any direction?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

extend current wherecondition like this

Where 
(
(ID = COALESCE(@ID, ID))
And ([TakeBack] = COALESCE(@TakeBack, [TakeBack]))
And (@TagLine Is Null Or [TagLine] Like '%' + @TagLine + '%')
And (@BusinessExtension Is Null Or [BusinessExtension] Like '%' + @BusinessExtension + '%')
And (@BusinessArea Is Null Or [BusArea] Like '%' + @BusinessArea + '%')
And (@Classification Is Null Or [PrimaryClassification] Like '%' + @Classification + '%' Or [SecondaryClassificationA1] Like '%' + @Classification + '%' Or [SecondaryClassificationA2] Like '%' + @Classification + '%'....)
And (@AgencyName Is Null Or [AgencyName] Like '%' + @AgencyName + '%')
And (@Contact Is Null Or [PrimaryContact] Like '%' + @Contact + '%' Or [SecondaryContact] Like '%' + @Contact + '%')
And (@ContactPhone Is Null Or [PrimaryContactPhone] Like '%' + @ContactPhone + '%' Or [SecondaryContactPhone] Like '%' + @ContactPhone + '%')
And (@ContactEmailAddress Is Null Or [ContactEmailAddress] Like '%' + @ContactEmailAddress + '%')
And (@WebSite Is Null Or [WebSite] Like '%' + @WebSite + '%')
And (@Address Is Null Or [Address] Like '%' + @Address + '%')
And (@City Is Null Or [City] Like '%' + @City + '%' Or [BusCity] Like '%' + @City + '%')
And (@Province Is Null Or [Province] Like '%' + @Province + '%' Or [BusProvince] Like '%' + @Province + '%')
And (@PostalCode Is Null Or [PostalCode] Like '%' + @PostalCode + '%')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources