-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDispatchWFCustomColumnsBoardDefaults](
@CurrentUser As varchar(10), 
@orderByClause As varchar(2000),
@filteredWhere as varchar(2000),
@RecordStart as nvarchar(50),
@RecordEnd as varchar(50),
@PageSize as varchar(50),
@PrimaryKey as varchar(100),
@StrColumns as varchar(2000)
)
AS
SET NOCOUNT ON

DECLARE @sqlDBcount varchar(20)
DECLARE @EmptyQuotes varchar(2)
SET @EmptyQuotes = ''''''

DECLARE @MainQuery nvarchar(max)
SET @MainQuery = 'SELECT LTRIM(RTRIM(STR(dbo.tbl_WFCustomDispatchBoardColumns.WFCustomBoardID))) AS WFCustomBoardID, dbo.tbl_WFCustomDispatchBoardColumns.SetupName, ISNULL(dbo.tbl_WFDispatchCustomBoardUserDefaults.DefaultValue, '''') AS DefaultValue,' +
					'ISNULL(dbo.tbl_WFDispatchCustomBoardUserDefaults.Exclude, ''N'') AS Exclude, dbo.tbl_WFCustomDispatchBoardColumns.SortType, ' + 
					'dbo.tbl_WFCustomDispatchBoardColumns.FieldName, dbo.tbl_WFDispatchCustomBoardUserDefaults.FK_UserId FROM dbo.tbl_WFCustomDispatchBoardColumns LEFT OUTER JOIN ' +
					'dbo.tbl_WFDispatchCustomBoardUserDefaults ON dbo.tbl_WFCustomDispatchBoardColumns.WFCustomBoardID = dbo.tbl_WFDispatchCustomBoardUserDefaults.WFCustomBoardID AND ' +
					'dbo.tbl_WFDispatchCustomBoardUserDefaults.FK_UserId=''' + @CurrentUser + ''' '


-- PRINT SUBSTRING(@MainQuery,0,4000)
 --PRINT SUBSTRING(@MainQuery,4000,4000)
 --PRINT SUBSTRING(@MainQuery,8000,4000)
 --PRINT SUBSTRING(@MainQuery,12000,4000)
 --PRINT SUBSTRING(@MainQuery,16000,4000)
 --PRINT SUBSTRING(@MainQuery,20000,4000)


DECLARE @FinalSQL nvarchar(max)
SET @FinalSQL = ' FROM (' + @MainQuery + ') z'
SET @FinalSQL = 'SELECT COUNT(WFCustomBoardID) AS Expr1 ' + @FinalSQL

-- You can't set the value from a dynamic sql statement into a variable.  You have to put it into a temp table and then retrieve it.
create table #temptable (CNT varchar(15) null)
insert into #temptable exec sp_executesql @FinalSQL
select @sqlDBcount = CNT from #temptable

SET @FinalSQL = ' FROM (' + @MainQuery + @filteredWhere + ') z'

---------Placing the Main SQL statement inside SQL1 for Paging--------------------------------------------
-- This only is compatible with 2012 due to the OFFSET FETCH clause
--SET @sql='Select rank() OVER ('+ @orderByClause +') as RowNumber,' + @sqlDBcount + ' AS TotalRows, (SELECT count(' + @PrimaryKey + ')' + @sql + ') AS TotalDisplayRows,' + @StrColumns + @sql + ' ' + @orderByClause + ' OFFSET ' + @RecordStart + ' ROWS FETCH NEXT ' + @PageSize +' ROWS ONLY'

SET @FinalSQL='Select rank() OVER ('+ @orderByClause +') as RowNumber,' + @sqlDBcount + ' AS TotalRows, (SELECT count(' + @PrimaryKey + ')' + @FinalSQL + ') AS TotalDisplayRows,' + @StrColumns + ' From ( Select *, ROW_NUMBER() OVER ( ' + @OrderByClause + ' ) rn FROM ( Select ' + @StrColumns + @FinalSQL + ' ) mq ) q WHERE (rn-1) BETWEEN ' + @RecordStart + ' AND ' + @RecordEnd+ ' ' + @OrderByClause

 --PRINT SUBSTRING(@FinalSQL,0,4000)
 --PRINT SUBSTRING(@FinalSQL,4000,4000)
 --PRINT SUBSTRING(@FinalSQL,8000,4000)
 --PRINT SUBSTRING(@FinalSQL,12000,4000)
 --PRINT SUBSTRING(@FinalSQL,16000,4000)
 --PRINT SUBSTRING(@FinalSQL,20000,4000)

EXEC sp_executesql @FinalSQL

--SET @MainQuery = replace(@MainQuery, '%WHERE%', CASE WHEN LEN(@filteredWhere) > 0 THEN @filteredWhere + ' AND ' + @WorkflowWhereClause ELSE ' WHERE ' + @WorkflowWhereClause END)
--SET @MainQuery = @MainQuery + @orderByClause
--PRINT SUBSTRING(@MainQuery,0,4000)
--PRINT SUBSTRING(@MainQuery,4000,4000)

--EXEC sp_executesql @MainQuery








GO