Use this one... I think u were missing conditions in between...

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[DateReport]

@DateStart nvarchar(50),

@EndDate nvarchar(50),

@CompName nvarchar(50)

AS

BEGIN

declare @Query nvarchar(4000)

set @Query ='Select [year].[month],

[year].[year],

[year].[money],

Tvarizi.[money] AS TVM,

Tvarizi.BillNum AS TVB,

Tvarizi.DateOfPayBill AS TDOPB,

Tcash.[Money] AS TCM,

Tcash.BillNum AS TCB,

Tcash.Recievedate AS TCRD,

case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end AS TCom,

case isnull(Tvarizi.WCashID,0) when 0 then TCashMoney.TotallValue else TvariziMoney.TotallValue end AS TMon

From [year]

Left outer join TVarizi On substring( TVarizi.DateOfPayBill,3,5)=[year].Date

Left outer join TCash On substring( TCash.Recievedate,3,5) = [year].Date

Left outer join TCompany as TCashTCompany On (TCashTCompany.CompanyName = TCash.companyName)

Left outer join TCompany as TvariziTCompany On (TvariziTCompany.CompanyName = Tvarizi.companyName)

Left outer join [Money] as TCashMoney On (TCashTCompany.CompanyName = TCashMoney.companyName)

Left outer join [Money] as TvariziMoney On (TvariziTCompany.CompanyName = TvariziMoney.companyName)

Where 1=1 '

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

set @Query = @Query + ' and (case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end ='''+@CompName+''')'

if isnull(@DateStart,'')<>'' and isnull(@EndDate,'')<>''

set @Query = @Query + ' and ([Year].Date between '''+@DateStart+''' and '''+@EndDate+''')'

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

set @Query = @Query + ' and ([Year].Date >='''+@DateStart+''')'

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

set @Query = @Query + ' and ([Year].Date <='''+@EndDate+''')'

set @Query = @Query + 'order by TCom, [year].[year], [year].[month]'

print @Query

exec (@Query)

END