ALTER procedure [dbo].[siriussp_GetAccountInvoicesWithPaging] 
        (@acctName CHAR(10),
         @Finalized BIT = 0,
         @PageSize INT = 15,
         @PageNumber INT = 1,
         @SearchText VARCHAR(max) = '',
         @OrderByColumn VARCHAR(20) = '',
         @SortOrder VARCHAR(4) = 'ASC', 
         @TotalRows INT OUTPUT)
as
begin
    set nocount on;

    IF OBJECT_ID('TempDb..#AccountInvoices', N'U') IS NOT NULL DROP TABLE #AccountInvoices;

    CREATE TABLE #AccountInvoices (invoice_no numeric(17,0) NOT NULL,
     Descrip VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL,
     hidden BIT NOT NULL, finalized BIT NOT NULL, Created DATETIME NULL,
     Balance MONEY, Rn Int)

     DECLARE @Sql NVARCHAR(MAX);

    SET @Sql = 'SELECT i.invoice_no, i.descrip1 as Descrip, i.hidden, i.finalized, i.date_time as Created,
    COALESCE(Bal.Balance,0) as Balance, ROW_NUMBER() OVER (ORDER BY ' + 
    CASE WHEN @OrderByColumn ='InvoiceNo' OR @OrderByColumn = '' THEN 'i.invoice_no' 
         WHEN @OrderByColumn = 'Descrip' THEN 'i.descrip1' 
         WHEN @OrderByColumn = 'Balance' THEN 'COALESCE(Bal.Balance,0)' 
         WHEN @OrderByColumn = 'Created' THEN 'i.date_time' END
         + CASE WHEN @SortOrder = 'DESC' THEN ' DESC' ELSE '' END  + 
        CASE WHEN @OrderByColumn ='InvoiceNo' OR @OrderByColumn = '' THEN ''  ELSE ', i.invoice_no' END +          
         ') AS Rn
    FROM dbo.invoices i 
    OUTER APPLY (SELECT SUM(extension) as Balance FROM dbo.transact t
    WHERE t.invoice_no = i.invoice_no) Bal
    WHERE i.account = @acctName ' + 
    CASE WHEN @Finalized = 0 THEN '' ELSE  ' AND i.finalized = 1' END  + 
    CASE WHEN COALESCE(@SearchText,'') = '' THEN '' ELSE CASE WHEN ISNUMERIC(@SearchText) = 1 THEN 
    
    ' AND (CAST(i.invoice_no AS VARCHAR(17)) LIKE @SearchText + ''%'' 
    OR i.descrip1 LIKE ''%'' + @SearchText + ''%'' 
    OR CAST(Bal.Balance AS VARCHAR(20)) LIKE @SearchText + ''%'')' 
    ELSE ' AND i.descrip1 LIKE ''%'' + @SearchText + ''%''' END 
    END;

    --PRINT @SQL;

    INSERT INTO #AccountInvoices (invoice_no, Descrip, hidden, finalized, Created, Balance, Rn)
    EXECUTE sp_executeSQL @Sql, N'@acctName CHAR(10), @SearchText VARCHAR(max)', @acctName = @acctName, @SearchText = @SearchText;

    SET @TotalRows = @@ROWCOUNT;

    SELECT invoice_no AS InvoiceNo, @acctName AS Account, Descrip, Hidden, Finalized, Created, Balance
      FROM #AccountInvoices WHERE Rn >= ((@PageNumber-1) * @PageSize + 1) AND Rn <= @PageNumber * @PageSize
    ORDER BY Rn;
    
END

/* Test
DECLARE @TotalRows INT = 0
execute dbo.siriussp_GetAccountInvoicesWithPaging @acctName = '*GUESTS*', @Finalized = 0,
 @TotalRows = @TotalRows OUTPUT, @SearchText = '', @SortOrder = 'ASC', @OrderByColumn = 'Balance'
SELECT @TotalRows
*/