Detailed description of how mysql stored procedures obtain return values in dynamic SQL _ MySQL

Detailed explanation of how mysql stored procedures obtain returned values in dynamic SQL statements bitsCN.com MySql general paging stored procedure
Process Parameters

P_cloumns varchar (500), p_tables varchar (100), p_where varchar (4000), p_order varchar (100), p_pageindex int, p_pagesize int, out p_recordcount int, out p_pagecount int

$: Begin
Declare v_sqlcounts varchar (4000 );
Declare v_sqlselect varchar (4000 );
# Concatenate SQL statements for querying total records
Set v_sqlcounts = concat ('SELECT count (*) into @ recordcount from ', p_tables, p_where );
# Select v_sqlcounts; leave $;
Set @ sqlcounts = v_sqlcounts;
Prepare stmt from @ sqlcounts;
Execute stmt;
Deallocate prepare stmt;
# Obtain the return value of a dynamic SQL statement
Set p_recordcount = @ recordcount;
# Calculate the total number of pages based on the total number of hops
Set p_pagecount = ceiling (p_recordcount + 0.0)/p_pagesize );
If p_pageindex <1 then
Set p_pageindex = 1;
Elseif p_pageindex> p_pagecount and p_pagecount <> 0 then
Set p_pageindex = p_pagecount;
End if;
# Splicing dynamic SQL statements for paging query records
Set v_sqlselect = concat ('select', p_cloumns, 'from', p_tables, p_where, if (p_order is not null, p_order, ''), 'limit', (p_pageindex-1) * p_pagesize, ',', p_pagesize );
# Select v_sqlselect; leave $;
Set @ sqlselect = v_sqlselect;
Prepare stmtselect from @ sqlselect;
Execute stmtselect;
Deallocate prepare stmtselect;
End $


# Concatenate SQL statements for querying total records
Set v_sqlcounts = concat ('SELECT count (*) into @ recordcount from ', v_tables, v_where );
Set @ sqlcounts: = v_sqlcounts;
# Preprocessing dynamic SQL statements
Prepare stmt from @ sqlcounts;
# Passing dynamic SQL parameters
Set @ s1 = categoryid;
Execute stmt using @ s1;
Deallocate prepare stmt;
# Obtain the return value of a dynamic SQL statement
Set recordcount = @ recordcount;

# Concatenate SQL statements for querying total records
Set v_sqlcounts = concat ('SELECT count (*) into @ recordcount from ', v_tables, v_where );
Set @ sqlcounts: = v_sqlcounts;
# Preprocessing dynamic SQL statements
Prepare stmt from @ sqlcounts;
# Passing dynamic SQL parameters
Set @ s1 = categoryid; execute stmt using @ s1; deallocate prepare stmt;
# Obtain the return value of a dynamic SQL statement
Set recordcount = @ recordcount;
I used dynamic SQL to add the number of queried count records to recordcount through the variable @ recordcount.
The if else of mysql is different from that of other databases. The following is a simple statement.

# Calculate the total number of pages based on the total number of hops
Set pagecount = ceiling (recordcount + 0.0)/pagesize );
If pageindex <1 then
Set pageindex = 1;
Elseif pageindex> pagecount then
Set pageindex = pagecount;
Else
Select pageindex, pagecount;
End if;

# Calculate the total number of pages based on the total record hops. set pagecount = ceiling (recordcount + 0.0)/pagesize); if pageindex <1 then set pageindex = 1; elseif pageindex> pagecount then set pageindex = pagecount; else select pageindex, pagecount; end if;
BitsCN.com