Searching Filter With Dynamic Query

Hi.

I am little bit weak in the field of Data Base .Many of you know this yet I want to share a DB stored procedure with .This SP helps you as a template when you have to Get some data with filters.


CREATE Procedure [dbo].[AdminOngoingAuction]
(
@BidID as smallint=1 , ---ProductId as mortgage or car insurance
@State as varchar(20)='',
@City as varchar(50)='',
@Zipcode as varchar(50)='',
@BuyerFName as varchar(100)='',
@BuyerLName as varchar(100)='',
@OpeningDate as datetime=null,
@ClosingDate as datetime=null,
@BidTimes as varchar(20)='',
@LoanTerm as int=0,
@PropertyValue as money=0,
@ResidenceStatus as int=0, --Propery Use ID
@LoanPurpose as int=0,
@LoanAmount as money=0,
@StartingBid as varchar(100)='',
@CurrentBid as varchar(100)='',
@totalbids as int=-1,
@FiveMinueRule as int=0,
@FrontDebtRatio as varchar='', -----
@BackDebtRatio as varchar='', -----
@CompletedDate as datetime=null, -----
@MaxLTV as varchar(5)='', -----
@MinLTV as varchar(5)='', -----
@MaxFicoScore as varchar(5)='',
@MinFicoScore as varchar(5)='', ----
@SSN as Bigint=0,
@BuyerEmail as varchar(100)=''

)
as
Begin
declare @query as varchar(Max)

if (len(@State)>2)
select @State=statename from state where statefname=@state

set @query= 'select Distinct(AUC.loanrequestid),(''AUC0'' + convert(varchar,BOI.LoanRequestID)) as AuctionID,AUC.BuyerID,AUC.StartDate,AUC.EndDate,
(select count(id) from auction where loanrequestid=AUC.LoanRequestId and bid is not null) as Totalbids,
(select min(bid) from auction where loanrequestid=AUC.LoanRequestId) as Currentbid,
(select max(bidsubmitdate) from auction where loanrequestid=AUC.LoanRequestId) as latestBidTime,
BOI.BorrowerFirstName + '' '' + BOI.BorrowerLastName as BuyerName,
(select BUd.EmailID as BuyerEmail from buyernotification BN inner join BuyerDetails BUd
on BN.BuyerId = BUd.BuyerId where Mail =''1'' and AuctionDisabled =''1'')as BuyerEmail,
BOI.City, BOI.State, BOI.ZIPID,
MLT.LoanAmount, MLT.NoOfmonth as LoanTerm, BLP.OpeningBid as StartingBid, PRI.LoanPurposeID,
PRI.PropertyUseID, PRI.OriginalCost as PropertyValue, (MOT.MortgageType + '', '' + convert(varchar,MLT.NoOfmonth) + '', '' + LOP.LoanPurpose) as MortgageType, MOT.MortgageType, MLT.InterestRate,

PRT.PropertyType from Auction AUC
inner join borrowerinformation BOI on BOI.loanrequestid=AUC.loanrequestid
inner join buyerloanprofile BLP on BLP.loanrequestid=BOI.loanrequestid
inner join propertyinformation PRI on BLP.loanrequestid=PRI.loanrequestid
inner join PropertyType PRT on PRT.PropertyTypeID=PRI.PropertyTypeID
inner join MortgageLoanTerm MLT on MLT.loanrequestid=PRI.loanrequestid
inner join MortgageType MOT on MOT.MortgageTypeID=MLT.MortgageTypeID
inner join LoanRequest LOR on LOR.loanrequestid=PRI.loanrequestid
inner join LoanPurpose LOP on LOP.LoanID=PRI.LoanPurposeID
inner join BuyerDetails BUD on BUd.BuyerID=LOR.BuyerID

where AUC.IsDisabled is Null and AUC.IsCompleted is Null and AUC.IsChallenged is Null and AUC.adminstatus is Null
and BUD.bidtype=' + convert(varchar, @bidID)

if (@State <>'')
set @query= @query + 'and BOI.State=''' + @State + ''''
if(@City<>'')
set @query= @query + 'and BOI.city=''' + @City + ''''
if(@Zipcode<>'')
set @query= @query + 'and BOI.zipid=''' + @Zipcode + ''''
if(@BuyerFName<>'')
set @query= @query + 'and BOI.BorrowerFirstName=''' + @BuyerFName + ''''
if(@BuyerLName<>'')
set @query= @query + 'and BOI.BorrowerLastName=''' + @BuyerLName + ''''
if(@SSN>0)
set @query= @query + 'and BOI.SSN=' + convert(varchar, @SSN)
if(@BuyerEmail<>'')
set @query= @query + 'and BUD.EmailId=''' + @BuyerEmail + ''''

if isdate(@OpeningDate)=1
set @query=@query + ' and CONVERT(CHAR(10),AUC.StartDate,101)=''' + CONVERT(CHAR(10),@OpeningDate,101) + '''' if isdate(@ClosingDate)=1
set @query=@query + ' and CONVERT(CHAR(10),AUC.EndDate,101)=''' + CONVERT(CHAR(10),@ClosingDate,101) + ''''
if(@LoanTerm > 0)
set @query= @query + 'and MLT.NoOfmonth=' + convert(varchar,@LoanTerm)

if(@MinFicoScore <>'')
set @query= @query + ' and MLT.FicoScore >='+ @MinFicoScore
if(@MaxFicoScore <>'')
set @query= @query + ' and MLT.FicoScore <=' + @MaxFicoScore

if (@FiveMinueRule =1)
set @query= @query + 'and AUC.FiveMinuteRule=1'
if (@FiveMinueRule =0)
set @query= @query + 'and (AUC.FiveMinuteRule=0 or AUC.FiveMinuteRule is null)'


if (@PropertyValue>0)
set @query= @query + 'and PRI.OriginalCost <=' + convert(varchar,@PropertyValue)
if(@ResidenceStatus >0)
set @query= @query + 'and PRI.PropertyUseID=' + convert(varchar,@ResidenceStatus)
if(@LoanPurpose >0)
set @query= @query + 'and PRI.LoanPurposeID=' + convert(varchar,@LoanPurpose)
if(@LoanAmount>0)
set @query= @query + 'and MLT.LoanAmount <=' + convert(varchar,@LoanAmount)
if(@StartingBid<>'')
set @query= @query + 'and BLP.OpeningBid=''' + @StartingBid + ''''
if(@CurrentBid<>'')
set @query= @query + 'and (select min(bid) from auction where loanrequestid=AUC.LoanRequestId) =''' + @CurrentBid + ''''
if @totalbids<>-1
set @query=@query + ' and (select count(bid) from auction where loanrequestid=AUC.LoanRequestId and bid is not null) = ' + CONVERT(varchar,@TotalBids)
if @BidTimes<>''
set @query=@query + ' and (select CONVERT(CHAR(5),max(bidsubmitdate),108) from auction where loanrequestid=AUC.LoanRequestId and bid is not null) = ''' + dbo.AddTimetToBidTime(@BidTimes) + ''''

Exec(@query)

End



Enjoy...
Sanjeev Chauhan
HelpOnDesk Team