ListJobs.sql

ALTER PROCEDURE [jobs].[spu_Job_List_Active_For_Recruiter] ( 
	@recruiterId INT,
	@pageNumber INT = 1, 
	@perPage INT = 5,
	@paging BIT = 1
) AS
BEGIN
	;WITH Jobs AS
	(
		SELECT 
			*
			,ROW_NUMBER() OVER (ORDER BY Date_Posted DESC) AS RowNumber
		FROM jobs.job
		WHERE 
			Recruiter_ID = @recruiterId 
		AND Job_Status_Code = 'A' -- active
	)
	SELECT
		j.*,
		js.*,
		jc.*
	FROM Jobs j
	INNER JOIN jobs.job_Category jc
	on jc.Job_Category_Id = j.Job_Category_ID
	LEFT JOIN jobs.Job_Stat js
	on js.Job_Id = j.Job_Id
	WHERE @paging = 1 AND RowNumber BETWEEN (@pageNumber - 1) * @perPage + 1 AND @pageNumber * @perPage
	ORDER BY RowNumber ASC;

END