字符串写法的存储过程

ALTER PROCEDURE [dbo].[sp_GetPlanList]
(
@CurrentDate varchar(50)=null,
@EnterpriseCode varchar(50)=null,
@PageIndex int ,
@PageSize int,
@PageCount int OUTPUT)
as
 begin
Declare @sql Nvarchar(max)
Set @sql='
 select * from (
  select
   row_number() over(order by x.CreateDate) num,
   x.PlanCode,
   x.BeginDate,
   x.EndDate,
   y.ProjectName,
   y.ProjectNum,
   y.Address,
   State=(case when exists(select 1 from ZJ_Plan z where z.PlanCode=x.PlanCode) then 1 else 0 end)
  from [Plan] x
  inner join Project y on x.ProjectID=y.ID

  where 1=1 and x.State=2'
  +case when @CurrentDate is not null then ' and cast(@CurrentDate as datetime)>=x.BeginDate and cast(@CurrentDate as datetime)<=x.EndDate ' else '' end
  +case when @EnterpriseCode is not null then ' and exists(select 1 from E_BaseRelation e where e.RegisterCode=x.OrganID and e.EnterpriseCode=@EnterpriseCode) ' else '' end
   + ' )t
 Where t.num between ((@PageIndex-1)*@PageSize+1) and @PageSize*@PageIndex

 Select @PageCount=count(1) from [Plan] x
         inner join Project y on x.ProjectID=y.ID
         where 1=1 and x.State=2 '
  +case when @CurrentDate is not null then ' and cast(@CurrentDate as datetime)>=x.BeginDate and cast(@CurrentDate as datetime)<=x.EndDate ' else '' end
  +case when @EnterpriseCode is not null then ' and exists(select 1 from E_BaseRelation e where e.RegisterCode=x.OrganID and e.EnterpriseCode=@EnterpriseCode) ' else '' end
print @SQL
Exec SP_Executesql @SQL,N'
@CurrentDate varchar(50)=null,
@EnterpriseCode varchar(50)=null,
@PageIndex int ,
@PageSize int,
@PageCount int OUTPUT',
 @CurrentDate,
 @EnterpriseCode,
 @PageIndex,
 @PageSize,
 @PageCount OutPut

end