Sp_Outstanding_Query (procedure)

未清存储过程查询、汇总、分页

• Type: PROCEDURE
• Deterministic: NO
• SQL data access: CONTAINS SQL

Parameters

#	Mode	Name	Type
1	IN	s_sql	longtext
2	OUT	sOutSql	longtext
3	IN	b_filter	longtext
4	IN	page_num	int
5	IN	page_size	int
6	OUT	totalCount	int
7	IN	count_cloumn	varchar(5000)
8	OUT	countMapJson	longtext
9	IN	sGroup_by	varchar(5000)
10	IN	sOrder_by	varchar(5000)
11	IN	sTable_alias	varchar(255)
12	IN	sGroupby_select_sql	varchar(5000)
13	IN	sGroupby_group_sql	varchar(5000)

Body

Body is not pre-cached. To inspect: mysql --defaults-file=~/.my.cnf -e 'SHOW CREATE PROCEDURESpOutstanding_Query'._

Narrative

Business context: Cross-module reporting helper — generic pager/aggregator that wraps a caller-built SELECT and returns the paged rows, the total row count and per-column totals as countMapJson. The COMMENT 未清存储过程查询、汇总、分页 (open-balance query, aggregation, pagination) describes its original purpose for 未清/未结 reports, but it is now the standard back-end of most paginated business reports.

What it does: Translates b_filter to a WHERE fragment via Fun_bFilter_toWhere(b_filter, sTable_alias) (default alias A); detects whether s_sql already contains a WHERE and either wraps it as select * from (s_sql sGroup_by) <alias> where 1=1 <bFilterWhere> or appends the filter directly; optionally re-wraps with sGroupby_select_sql ... sGroupby_group_sql for grouped reports. Runs SELECT COUNT(1) INTO @totalCountTmp FROM (...) into totalCount. For every column in the comma-separated count_cloumn list it builds a SUM/aggregation into countMapJson, with a separate code path for tree_* columns that need tree-aware rollups. Finally re-emits the full SQL into sOutSql for the caller to PREPARE/EXECUTE.

Invocation: Called by ~96 sibling routines — most paginated report procs (e.g. Sp_Daysalecustomer, Sp_DaysaleSalesMan, Sp_Inventory_*, Sp_Sales_*, Sp_Payables_*, Sp_Receivables_*, Sp_OverdueNo*, Sp_OneDatePlan_Machine, etc.) hand their dynamic SQL string + paging/grouping inputs to this proc and use the OUT sOutSql. No xly-src Java caller — it is purely chained from other procedures.