CREATE Procedure [dbo].[spTestingBaseVoter]
   @SortColumn    NVARCHAR(128) = N'name_voter',
   @SortDirection VARCHAR(4)    = 'asc',
   @offset INT,
   @limit INT
As
Begin
  SET NOCOUNT ON;

  -- reject any invalid sort directions:
  IF LOWER(@SortDirection) NOT IN ('asc','desc')
  BEGIN
    RAISERROR('Invalid parameter for @SortDirection: %s', 11, 1, @SortDirection);
    RETURN -1;
  END

  -- reject any unexpected column names:
  IF LOWER(@SortColumn) NOT IN (N'name_voter', N'home_street_address_1', N'home_address_city')
  BEGIN
    RAISERROR('Invalid parameter for @SortColumn: %s', 11, 1, @SortColumn);
    RETURN -1;
  END

  --SET @SortColumn = QUOTENAME(@SortColumn);

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'SELECT id, name_voter, home_street_address_1, home_address_city
             FROM dbo.base_voter
    WITH(NOLOCK)
    WHERE deleted_at IS NULL'

  SET @sql = @sql + N' ORDER BY ' + @SortColumn + ' ' + @SortDirection +
    ' OFFSET @OF ROWS
    FETCH NEXT @LIM ROWS ONLY ';



  EXEC sp_executesql @sql,
     N'@OF int,@LIM int',
     @OF=@offset, @LIM=@limit
End

CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
  ON dbo.base_voter (name_voter asc,home_street_address_1, home_address_city)
  WHERE deleted_at IS NULL ;

Execute spTestingBaseVoter name_voter,asc,9999950,50