MySql Stored Procedure processing with Parameters

The MySql stored procedure described below is a stored procedure with parameters (dynamic execution of SQL statements). This MySql Stored Procedure queries user information based on user input conditions and sorting methods, there is no call Method for sorting conditions:

Call GetUsersDynamic (age <= 30 ,);

 

 
 

  
  
/******** Dynamically query user information ********/
  
  
Create procedure GetUsersDynamic (WhereCondition varchar (500), OrderByExpress varchar (100 ))
  
  
Begin
  
  
Declare stmt varchar (2000 );
  
  
If LENGTH (OrderbyExpress)> 0 then
  
  
Begin
  
  
Set @ sqlstr = concat (select id, name, password, age, getdate (adddate) as AddDate from users where, WhereCondition, order by, OrderByExpress );
  
  
End;
  
  
Else
  
  
Begin
  
  
Set @ sqlstr = concat (select id, name, password, age, getdate (adddate) as AddDate from users where, WhereCondition );
  
  
End;
  
  
End if;
  
  
Prepare stmt from @ sqlstr;
  
  
Execute stmt;
  
  
End;
 
 

Getdate () is a custom function that returns the short format of a date.

 

 
 

  
  
CREATE DEFINER=`root`@`localhost` FUNCTION `getdate`($date datetime) RETURNS varchar(50) CHARSET latin1    
  
  
return date_format($date,%Y-%m-%d);  
 
 

MySql Stored Procedure for dynamic data insertion (note that four single quotes indicate one single quotation mark ):

 

 
 

  
  
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertUser`(in name2 varchar(50),in password2 varchar(32),in age2 int,in adddate2 datetime)    
  
  
begin    
  
  
DECLARE stmt varchar(2000);    
  
  
set @sqlstr=concat(insert into users(name,password,age,adddate) values();    
  
  
set @sqlstr=concat(@sqlstr,,name2,,,,,password2,,,,age2,,,,adddate2,,));    
  
  
prepare stmt from @sqlstr;    
  
  
execute stmt;    
  
  
end;