-- ============================================= -- Author: Gartle LLC -- Release: 10.0, 2022-07-05 -- Description: The procedure selects data from s17.requests -- ============================================= CREATE PROCEDURE [s17].[usp_request] @category_id int = NULL , @time_id int = NULL , @entity_id int = NULL , @rows int = NULL AS BEGIN SET NOCOUNT ON IF @rows IS NULL SET @rows = 5 DECLARE @row_count int = COALESCE(( SELECT MAX(t.row_index) AS row_index FROM s17.requests t WHERE t.entity_id = @entity_id AND t.category_id = @category_id AND t.time_id = @time_id AND t.is_empty = 0 ), 0) IF @row_count < @rows SET @row_count = @rows ;WITH e1(n) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), -- 10 e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10 e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100 SELECT n.row_index , t.row_format , t.comment , t.account_id , t.subaccount_id , t.product_id , t.region_id , '=[@01]+[@02]+[@03]+[@04]+[@05]+[@06]+[@07]+[@08]+[@09]+[@10]+[@11]+[@12]' AS total , t.[01] , t.[02] , t.[03] , t.[04] , t.[05] , t.[06] , t.[07] , t.[08] , t.[09] , t.[10] , t.[11] , t.[12] , t.row_formulas , t.row_comments FROM (SELECT ROW_NUMBER() OVER (ORDER BY n) AS row_index FROM e3) n LEFT OUTER JOIN s17.requests t ON t.row_index = n.row_index AND t.entity_id = @entity_id AND t.category_id = @category_id AND t.time_id = @time_id WHERE n.row_index BETWEEN 1 AND @row_count ORDER BY n.row_index END