How to create Dynamic Stored Procedure in Sql

 Dynamic Stored Procedure
======================================

CREATE proc [dbo].[select_profile] --'All','All','All',18,70,122,200,'All','All','All','All','All','All'      
(          
           
  @userid nvarchar(max) ,
  @viewed nvarchar(max),
  @photo nvarchar(max),
  @minage int,
  @maxage int,
  @minhieght int,
  @maxhieght int,
  @education nvarchar(max),
  @occupation nvarchar(max),
  @location nvarchar(max),
  @deit nvarchar(max),
  @maritalstatus nvarchar(max),
  @religion nvarchar(max)
  )          
as          
--begin    
Declare @query nvarchar(max)='select a.email,a.name,b.imagepath,g.city,g.height,g.birthyear,
       h.cast,h.religion,j.income,j.workarea,d.eduback
       from accountdetails as a left outer join userimage as b ON a.email=b.email
       join contactdetail as c ON a.email=c.email join educationdetail as d ON a.email=d.email
       join familydetail as e ON a.email=e.email join partnerdetail as f ON a.email=f.email
       join personaldetail as g ON a.email=g.email join socialdetail h on a.email=h.email
       join socialreligious as i on a.email=i.email join yourself as j on a.email=j.email where b.isactive=1'
     
    -- print @query
 if(@userid='All')
 begin          
 set @query=@query          
 end
 else        
 begin          
 set @query=@query+'and a.email not in(select femail from expressinterest where isactive=0 and email='+''''+@userid+''')
       and a.email not in(select femail from shortlist where isactive=1 and email='+''''+@userid+''')'      
 end          
 if(@viewed='All')          
 begin          
 set @query=@query          
 end
 if(@viewed='Viewed')        
 begin          
 set @query=@query+'and a.email in(select femail from viewed where email='+''''+@userid+''')'      
 end  
  if(@viewed='Not Viewed')          
 begin          
 set @query=@query+'and a.email not in(select femail from viewed where email='+''''+@userid+''')'
 end
 if(@photo='All')
 begin
 set @query=@query          
 end
 if(@photo='With Photo')
 begin
 set @query=@query+'and a.email not in(select email from userimage where isactive=1 and img=1)'
 end

 if(@minage>17)
 begin
 set @query=@query+'and a.email in(select email from personaldetail where age>='+ convert(varchar,@minage)+' AND age<='+convert(varchar,@maxage)+')'
 end

 if(@minhieght>121)
 begin
 set @query=@query+'and a.email in(select email from personaldetail where height>='+convert(varchar,@minhieght)
 +' AND height<='+convert(varchar,@maxhieght)+')'
 end

 if(@education='All')
 begin
 set @query=@query
 end
 else
 begin
 set @query=@query+'and a.email in(select email from educationdetail
  where eduback in (select * from fn_ParseCSVString('''+@education+''','','''+'))) '
 end
 if(@occupation='All')
 begin
 set @query=@query
 end
 else
 begin
 set @query=@query+'and a.email in(select email from yourself
 where workarea in (select * from fn_ParseCSVString('''+@occupation+''','','''+'))) '
 end

 if(@location='All')
 begin
 set @query=@query
 end
 else
 begin
 set @query=@query+'and a.email in(select email from personaldetail
 where city in (select * from fn_ParseCSVString('''+@location+''','','''+'))) '
 end

 if(@deit='All')
 begin
 set @query=@query
 end
 else
 begin
 set @query=@query+'and a.email in(select email from yourself
 where diet in (select * from fn_ParseCSVString('''+@deit+''','','''+'))) '
 end

 if(@maritalstatus='All')
 begin
 set @query=@query
 end
 else
 begin
 set @query=@query+'and a.email in(select email from socialdetail
 where marital in (select * from fn_ParseCSVString('''+@maritalstatus+''','','''+'))) '
 end

 if(@religion='All')
 begin
 set @query=@query
 end
 else
 begin
 set @query=@query+'and a.email in(select email from socialdetail
 where religion in (select * from fn_ParseCSVString('''+@religion+''','','''+'))) '
 end

print(@query)
     
execute sp_executesql @query

==================================================
 
         
CREATE proc selectby_datawarehousing  --0,'','','','','','','','','','', '',''      
(          
           
  @CityID bigint,      
  @Sector varchar(100),      
  @Road varchar(100),      
  @frmtotalbuiltupArea varchar(100),      
  @toTotalbuiltupArea varchar(100),    
  @frmTotalplotArea varchar(100),    
  @toTotalplotArea varchar(100),    
  @frmopenArea varchar(100),    
  @ToOpenArea varchar(100),            
  @Rentmin varchar(200),      
  @Rentmax varchar(200),    
  @Floor varchar(100),    
  @AreaUnit varchar(100)              
)          
as          
begin          
Declare @query nvarchar(max)='Select Distinct WareHouseMaster.WareHouseID,WareHouseMaster.Title,WareHouseMaster.WareHousePropertyID,WareHouseMaster.PersonName,WareHouseMaster.CompanyName,WareHouseMaster.ContactNumber,WareHouseMaster.EmailID,  
WareHouseMaster.AddressesId,WareHouseMaster.AccessRoadWidth,WareHouseMaster.AccessRoadUnit,WareHouseMaster.Height,WareHouseMaster.AvailableForID,          
WareHouseMaster.PropertyTypeID,WareHouseMaster.PartiallyAvailable, ISNULL(convert(varchar(300),WareHouseMaster.TotalBuiltUpArea),'''')+ ''''+ISNULL(WareHouseMaster.BuiltUpAreaUnit,'''') As TotalBuiltUpAreaWithUnit,      
                                   
CONVERT(Varchar(2000),WareHouseMaster.OpenArea)+''''+ISNULL(WareHouseMaster.OpenAreaUnit,'''') As OpenAreaWithUnit,wareHouseMaster.RentOpen, RoofTypesMaster.RoofType,RentOpen +''''+ RentOpenUnit As RentOpenWithUnit,Convert(Varchar(2000),  
WareHouseMaster.PlotArea)+''''+Convert(Varchar(2000),WareHouseMaster.AreaUnit) As PlotAreaWithUnit,ISNULL(Addresses.UnitNo,'''')+''''+ISNULL(Addresses.BuildingName,'''')+''''+ISNULL(Addresses.Sector,'''')+''''+ISNULL(Addresses.Road,'''')+''''+ISNULL(Addre
sses.[Floor],'''')  
   
as [Address] , PropertyAvailableFor.AvailableFor,PropertyTypeMaster.PropertyType , MemberMaster.Firstname+ISNULL(LastName,'''') As Member,                
CONVERT(VARCHAR(20), WareHouseMAster.EnteredOn, 100) as EnteredOnString ,CONVERT(VARCHAR(20), WareHouseMAster.UpdatedOn, 100) as UpdatedOnString,                                        
                   
ISNULL(WareHouseMaster.EnteredOn,WareHouseMaster.UpdatedOn) As T,ISNULL(WareHouseMaster.IsActive,0)as showdetails,Addresses.UnitNo,Addresses.BuildingName,Addresses.Sector,Addresses.Road,Addresses.Floor,          
CityMaster.City,CityMaster.CityID,WareHouseMaster.GranterOfLease,convert(varchar(20),WareHouseMaster.LeaseExpiringOn,106)as LeaseExpiringDate,          
CONVERT(VARCHAR(20), WareHouseMAster.EnteredOn, 106)as Enterdate,PropertyAvailableFor.AvailableFor,PropertyTypeMaster.PropertyType                              
From WareHouseMaster                                                  
Inner Join Addresses on Addresses.AddressId=WareHouseMaster.AddressesId                                                  
Inner Join PropertyAvailableFor on PropertyAvailableFor.AvailableForId=WareHouseMaster.AvailableForID                                                  
Inner Join PropertyTypeMaster on PropertyTypeMaster.PropertyTypeID=WareHouseMaster.PropertyTypeID                                                
Inner Join MemberMaster on MemberMaster.MemberID=WareHouseMaster.EnteredBy          
inner join CityMaster on CityMaster.CityID=Addresses.CityID          
Inner Join RoofTypesMaster on RoofTypesMaster.RoofTypeID=WareHouseMaster.RoofTypeId    
inner join BuiltUpAreaDetails on BuiltUpAreaDetails.WareHouseID=WareHouseMaster.WareHouseID    
where WareHouseMaster.IsActive=1 and Addresses.IsActive=1 and MemberMaster.IsActive=1 and CityMaster.IsActive=1 and BuiltUpAreaDetails.IsActive=1'      
     
     
     
 if(@CityID<>0)          
 begin          
 set @query=@query+'And CityMaster.CityID='+CONVERT(varchar,@CityID)          
 end          
   
 if(@Sector is not null and @Sector!='')          
 begin          
 set @query=@query+'And Addresses.Sector='''+@Sector+'''';          
 end          
           
           
 if(@Road is not null and @Road!='')          
 begin          
 set @query=@query+'And Addresses.Road='''+@Road+'''';          
 end    
     
     
 if(@frmtotalbuiltupArea is not null and @frmtotalbuiltupArea!='' or @toTotalbuiltupArea is not null and @toTotalbuiltupArea!='')    
 begin    
 set @query=@query+'and WareHouseMaster.TotalBuiltUpArea between '''+@frmtotalbuiltupArea+''' and '''+@toTotalbuiltupArea+'''';    
 end    
     
 if(@frmTotalplotArea is not null and @frmTotalplotArea!='' or @toTotalplotArea is not null and @toTotalplotArea!='')    
 begin    
 set @query=@query+ 'and WareHouseMaster.PlotArea between '''+@frmTotalplotArea+''' and '''+@toTotalplotArea+'''';    
 end    
     
 if(@frmopenArea  is not null and @frmopenArea!='' or @ToOpenArea is not null and @ToOpenArea!='')    
 begin    
 set @query=@query + 'and WareHouseMaster.OpenArea between '''+@frmopenArea+''' and '''+@ToOpenArea+'''';    
 end    
     
 if(@Rentmin is not null and @Rentmin!='' or @Rentmax is not null and @Rentmax!='')    
 begin    
 set @query=@query+ 'and BuiltUpAreaDetails.Rent between '''+@Rentmin+''' and  '''+@Rentmax+'''';    
 end    
     
           
 if(@Floor is not null and @Floor!='')          
 begin          
 set @query=@query+'And BuiltUpAreaDetails.Floor='''+@Floor+'''';          
 end          
       
   if(@AreaUnit is not null and @AreaUnit!='')    
   begin    
   set @query=@query + 'and WareHouseMaster.AreaUnit='''+@AreaUnit+'''';    
   end    
       
           
 execute sp_executesql @query          
end