|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
DECLARE @Page INT = 1 , @PageSize INT = 50 , @LanguageID INT = 46 , @ObjectID UNIQUEIDENTIFIER = '27461EA1-6EAC-47C5-8500-50A411C440D6' , @Sort_Key BIT = NULL , @Sort_Default BIT = NULL , @Sort_Translation BIT = NULL , @Filter_Key NVARCHAR(50) = NULL , @Filter_Default NVARCHAR(255) = NULL , @Filter_Translation NVARCHAR(MAX) = NULL SELECT [KeyStore].[ID] AS [KeyStore_ID] , [KeyStore].[Key] AS [KeyStore_Key] , [KeyStore].[Default] AS [KeyStore_Default] , [KeyStore].[ApplicationID] AS [KeyStore_ApplicationID] , [Value].[ID] AS [Value_ID] , [Value].[Value] AS [Trans] , [Value].[LanguageID] AS [Value_LanguageID] , [Language].[EnglishName] AS [Language_EnglishName] , TotalRows = COUNT(*) OVER() -- Return Total Rows. Without Offset FROM [Translation].[KeyStore] LEFT JOIN [Translation].[Value] [Value] ON [Value].[KeyStoreID] = [KeyStore].[ID] INNER JOIN [Translation].[Language] [Language] ON [Language].[ID] = @LanguageID WHERE ([ApplicationID] = @ObjectID AND ([Value].LanguageID IS NULL OR [Value].LanguageID = @LanguageID)) -- Filter: Key AND ( (@Filter_Key IS NULL OR @Filter_Key = '') OR ([KeyStore].[Key] LIKE REPLACE(@Filter_Key, '*', '%') OR (DIFFERENCE([KeyStore].[Key], @Filter_Key) > 3) ) ) -- Filter: Default AND ( (@Filter_Default IS NULL OR @Filter_Default = '') OR ([KeyStore].[Default] LIKE REPLACE(@Filter_Default, '*', '%') OR (DIFFERENCE([KeyStore].[Default], @Filter_Default) > 3) ) ) -- Filter: Translation AND ( (@Filter_Translation IS NULL OR @Filter_Translation = '') OR ([Value].[Value] LIKE REPLACE(@Filter_Translation, '*', '%') OR (DIFFERENCE([Value].[Value], @Filter_Translation) > 3) ) ) ORDER BY -- Sort: Key CASE @Sort_Key WHEN 0 THEN [KeyStore].[Key] END DESC, CASE @Sort_Key WHEN 1 THEN [KeyStore].[Key] END ASC -- Sort: Default , CASE @Sort_Default WHEN 0 THEN [KeyStore].[Default] END DESC, CASE @Sort_Default WHEN 1 THEN [KeyStore].[Default] END ASC -- Sort: Value , CASE @Sort_Translation WHEN 0 THEN [Value].[Value] END DESC, CASE @Sort_Translation WHEN 1 THEN [Value].[Value] END ASC -- Default Sort , CASE WHEN @Sort_Key IS NULL AND @Sort_Default IS NULL AND @Sort_Translation IS NOT NULL THEN [KeyStore].[Key] END ASC -- Offset paging OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY |
Login