CREATE PROCEDURE Admin_Inst_SelectInst
(
	@FilterCityID int = 0,
	@PageSize int = 10,
	@PageNumber int = 1,
	@SortColumn varchar(15) = "InstID",
	@SortOrder varchar(4) = "ASC"
) 
AS

	SET NOCOUNT ON

	DECLARE @FirstRecord int
	DECLARE @RecordCount int
	DECLARE @TempSize int
	DECLARE @StartID int
	
	CREATE TABLE #TempItems
	(
		RecID int IDENTITY,
		InstID int,
		InstName varchar(50),
		CityID int,
		CityName varchar(50),
		CreatedDate datetime
	)
	
	SELECT @RecordCount = Count(InstID) FROM viewInstitutions 
	WHERE 
		CityID >= @FilterCityID AND 
			(CityID * @FilterCityID = @FilterCityID OR CityID = @FilterCityID)

	SET @FirstRecord = (@PageNumber-1) * @PageSize + 1
	SET @TempSize = (@PageNumber-1) * @PageSize +  @PageSize

	IF(@FirstRecord <= @RecordCount)
	BEGIN
	    SET ROWCOUNT @TempSize
	    
	    IF (@SortOrder = 'ASC')
			INSERT INTO #TempItems(InstID,InstName,CityID,CityName,CreatedDate)
			SELECT InstID,InstName,CityID,CityName,CreatedDate FROM viewInstitutions
				WHERE 
					CityID >= @FilterCityID AND 
					(CityID * @FilterCityID = @FilterCityID OR CityID = @FilterCityID)
				ORDER BY
				CASE @SortColumn
					When 'InstID' Then InstID
					When 'InstName' Then InstName
					When 'CityName' Then CityName
					When 'CreatedDate' Then CreatedDate
				END
			ASC
		ELSE
			INSERT INTO #TempItems(InstID,InstName,CityID,CityName,CreatedDate)
			SELECT InstID,InstName,CityID,CityName,CreatedDate FROM viewInstitutions
				WHERE 
					CityID >= @FilterCityID AND 
					(CityID * @FilterCityID = @FilterCityID OR CityID = @FilterCityID)
				ORDER BY
				CASE @SortColumn
					When 'InstID' Then InstID
					When 'InstName' Then InstName
					When 'CityName' Then CityName
					When 'CreatedDate' Then CreatedDate
				END
			DESC
	    
	    
	    SET ROWCOUNT @PageSize
	    
		Select * From #TempItems where RecID >=@FirstRecord

	END
	ELSE
	BEGIN
		Select * From #TempItems
	END

	--Select * From Cities order by CityName asc
	
	SET NOCOUNT OFF
GO