SQL储存过程的代码

create proc p_show
(
    @pageindex int,
    @pagesize int,
    @ename nvarchar(20),
    @totalcount int out,
    @totalpage int out
)
as
begin
    declare @sql nvarchar(max)='select a.*,b.DName,c.CName as Province,d.CName as City,e.CName as County from Employee a join Department b on a.DId=b.DId join City c on a.ProvinceId=c.CId join City d on 
    a.CityId=d.CId join City e on a.CountyId=e.CId'
    declare @sqlcount nvarchar(max)='select @totalcount=count(0) from Employee a join Department b on a.DId=b.DId join City c on a.ProvinceId=c.CId join City d on 
    a.CityId=d.CId join City e on a.CountyId=e.CId'
    if(@ename!='')
    begin
        set @sql+=' and a.EName like ''%'+@ename+'%'''
        set @sqlcount+=' and a.EName like ''%'+@ename+'%'''
    end
    set @sql=' order by a.EId offset (@pageindex-1)*pagesize rows fetch next @pagesize rows only'
    exec sp_executesql @sql,N'@pageindex int,@pagesize int',@pageindex,@pagesize
    exec sp_executesql @sqlcount,N'@totalcount int out',@totalcount out
    set @totalpage=CEILING(@totalcount/1.0/@pagesize)
end