Click here to Skip to main content
15,901,122 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
alter procedure ListEmployeedtls_Paging

Declare @result nvarchar(700);
Declare @orderby nvarchar(700);
Declare @PageSize nvarchar(200);
Declare @PageNum nvarchar(200);
Declare @Offset nvarchar(200);
--set @orderby=' order by e.EmpId desc';

set @PageSize=10
set @PageNum=2
set @Offset=@PageSize*(@PageNum-1)

set @Result='select Top '+@PageSize+' e.EmpId
,(e.FirstName+'' ''+e.LastName) as EmpName

from Employeedtls e left outer join Department d on d.DeptId=e.DepartmentId
left outer join Grade g on g.GradeId=e.GradeId 
left outer join User_Role r on r.RoleId=e.RoleId
left outer  join Employeedtls s on e.SupervisorId=s.EmpId 
where 1=1  and e.EmpId not in

 (select  top '+@Offset+' e1.EmpId
from Employeedtls e1 left outer join 
Department d1 on d1.DeptId=e1.DepartmentId
left outer  join Employeedtls s1 on e1.SupervisorId=s1.EmpId
left outer join Grade g1 on g1.GradeId=e1.GradeId 
left outer join User_Role r1 on r1.RoleId=e1.RoleId
 where 1=1)'

print @Result
print @Result+ @orderby

exec (@Result + @orderby)

What I have tried:

Thanks for the help in Advance
Updated 29-May-17 1:17am

1 solution

@ Result is too small to hold it all, so it's truncated. Try this:
Declare @result nvarchar(1000);
Share this answer
Member 10013883 30-May-17 3:18am    
Thank you
OriginalGriff 30-May-17 3:28am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900