Pages

Tuesday 11 March 2014

Dynamically deciding where clause of the store procedure

ALTER procedure [dbo].[SpAmsGenerateReport2]
(
@ProgramId varchar(10) =null
--Buyer
, @BuyerId varchar(10) = null
--Seller
, @SellerId varchar(10) = null
--FromDate
, @rights_fromdate datetime=null
--ToDate
, @rights_todate datetime=null
--RightsId
, @RightsId varchar(250)=null
--FilmCrew
, @FilmCrew varchar(10)=null
--Country
, @CountryId varchar(7500)=null
)
as
declare
@Query as varchar(max)
begin
--IF ISNULL(@CountryId,'') = ''
--begin
-- SET @CountryId = '""' ;
--end
set @Query='select p.ProgramName as Movie,
sb1.SellerBuyerName as Buyer,
sb2.SellerBuyerName as Seller,
a.AgreementDate as AgreementDate,
r.rights_fromdate as FromDate,
r.rights_todate as ToDate,
rights.RightsName as RightsName,
r.inc_desc as IncludedTerritory,
r.exc_desc as ExcludedTerritory,
r.rights_todate as ExpiryDate,
r.special_stip as SpecialStipulation
from AMSReport_table r
join AMSProgram p
on p.ProgramId=r.programme_code
join AMSSellerBuyer sb1
on r.company_Code=sb1.SellerBuyerId
join AMSSellerBuyer sb2
on r.party_code=sb2.SellerBuyerId
join AMSAgreement a
on a.AgreementNumber=r.agreement_no
join AMSRights rights
on rights.RightsId=r.rights_code
where '
if(@rights_fromdate<>'')
begin
set @Query=@Query + ' r.rights_fromdate >= ' + '''' + convert(varchar(11),@rights_fromdate,106) + ''''
end
else
begin
set @Query=@Query + ' r.rights_fromdate >= 01/01/1900'
end
if(@rights_todate<>'')
begin
set @Query=@Query +' and r.rights_todate <= ' + '''' + convert(varchar(11),@rights_todate,106)+''''
end
if(@ProgramId <> '')
begin
set @Query=@Query + ' and r.programme_code = '+@ProgramId
end
if(@BuyerId <> '')
begin
set @Query=@Query+ ' and r.company_Code = '+@BuyerId
end
if(@SellerId <> '')
begin
set @Query=@Query+ ' and r.party_code = '+@SellerId
end
if(@CountryId <> '')
begin
set @Query=@Query+ ' and r.included_territory like '+ ''''+'%~' +@CountryId + '~%' + ''''
end
if(@RightsId <>'')
begin
set @Query=@Query+ ' and r.rights_code in ('+@RightsId+')'
end
if(@FilmCrew<>'')
begin
set @Query=@Query+ ' and ( p.ProducerId in ('+@FilmCrew+')
or p.DirectorId in ('+@FilmCrew+')
or p.BannerId in ('+@FilmCrew+')
or p.MusicCompanyId in ('+@FilmCrew+')
or p.StarCasts like '+''''+'%'+@FilmCrew+'%'+''''+')'
end
--select (@Query)
execute(@Query)
end

No comments:

Post a Comment