How To Create Dynamic Store Procedure More Angle Filterable Store Procedure?

Dynamic store Procedure example

ALTER PROCEDURE [dbo].[Dynamic_Store_Procedure]

      -- Add the parameters for the stored procedure here

 @startdate as datetime,

 @enddate as datetime,

 @than as Nvarchar(MAX)='>',

 @num as int=0,

 @khojitype as Nvarchar(50),

 @sharvanthanName as Nvarchar(max),

 @actual bit=1,

 @seva bit=1

AS

BEGIN

 DECLARE @SQLString Nvarchar(max)

set @SQLString='select khojiname,totalpravachan,sevacount,Pointscount,MAdate,sharvanthanName,Khojitype,MobileNo from (

select kh.khojiname,isnull(temp.sc,0) as totalpravachan,Convert(Varchar,kh.Madate,103)as MAdate,sm.sharvanthanName ,kh.Khojitype,kh.MobileNo

,isnull(seva.sevacount,0) as sevacount,isnull(seva.points,0)as Pointscount from khojimaster kh left outer join

(select ad.khojiid,count(*) as sc from attendencedetail ad left outer join

attendence a on ad.attendenceid = a.attendenceid Where 1=1'

If Isnull(@startdate,'')!=''

BEGIN

      SET @SQLString = @SQLString + ' And a.attendenceDate >=     ''' + convert(nvarchar(10),@startdate,101) + ''''

end

If Isnull(@enddate,'')!=''

BEGIN

      SET @SQLString = @SQLString + ' AND a.attendenceDate <=     ''' + convert(nvarchar(10),@enddate,101) + ''''      

END

SET @SQLString = @SQLString + ' GROUP BY ad.khojiid '

--SET @SQLString =@SQLString + ' Having Count(*) >= ''' + convert(Nvarchar(20),isnull(@num,0))+ ''' '

 SET @SQLString =@SQLString + ' Having

(Case 

 When ('''+ @than + ''')=''>'' Then Count(*) End)>= ''' + convert(Nvarchar(20),isnull(@num,0))+ '''

OR

(Case

When ('''+ @than + ''')=''<'' Then Count(*) End)<= ''' + convert(Nvarchar(20),isnull(@num,0))+ ''' '

SET @SQLString =@SQLString + ' )temp on temp.khojiid = kh.khojiid '

SET @SQLString =@SQLString + 'left outer join(select khojiid,count(*)as sevacount,sum(stm.Points)as points  from sevadetail sd left outer join sevaMaster

sm on sm.sevamasterid=sd.sevamasterid left outer join sevatypemaster

stm on stm.SevaTypeId=sm.SevaTypeId  Where 1=1 '

If Isnull(@startdate,'')!=''

BEGIN

      SET @SQLString = @SQLString + ' And sm.sevadate >= ''' + convert(nvarchar(10),@startdate,101) + ''''

end

If Isnull(@enddate,'')!=''

BEGIN

      SET @SQLString = @SQLString + ' AND sm.sevadate <= ''' + convert(nvarchar(10),@enddate,101) + ''''      

END

SET @SQLString = @SQLString + ' group by khojiid)seva on seva.khojiid=kh.khojiid  '

SET @SQLString =@SQLString + 'Left outer join sharvanthanMaster as sm on sm.sharvanthanid=kh.sharvanthanid Where 1=1 '

if isnull(@khojitype,'')<>''

Begin

set @SQLString =@SQLString + ' And kh.khojitype in (select member_id from  SplitString('''+ @khojitype + ''','',''))'

End

if isnull(@sharvanthanName,'')<>''

Begin

set @SQLString =@SQLString + ' And sm.sharvanthanName In (select member_id from SplitString('''+ @sharvanthanName +''','',''))) p Where 1=1'

End

if isnull(@actual,0)=0

Begin

set @SQLString =@SQLString + ' And totalpravachan >'''+ convert(Nvarchar(20),isnull(@actual,0))  + ''''

End

if isnull(@seva,0)=0

Begin

set @SQLString =@SQLString + ' And sevacount >'''+ convert(Nvarchar(20),isnull(@seva,0))  + ''''

End

set @SQLString =@SQLString + ' Order by  khojiName'

Exec(@SQLString)

--print @SQLString

END