MySQL stored procedure with parameter processing method

The mysql stored procedure described below is a stored procedure with parameters (Dynamic execution of SQL statements) that queries the user's information based on the conditions and sort of user input, and the sorting criteria can be not invoked:

Call Getusersdynamic (' age<=30 ', ');

 
 

  
  

  
/******** dynamically query the user's information ********/
  
  

  
CREATE PROCEDURE getusersdynamic (wherecondition varchar), 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 the 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 a short form of the date

 
  
  

  
   
   
CREATE definer= ' root ' @ ' localhost ' FUNCTION ' getdate ' ($date datetime) RETURNS varchar (m) CHARSET
  
   
   
Return Date_format ($date, '%y-%m-%d ');
 
  
  

The mysql stored procedure for dynamically inserting data (note that four single quotes denote one quotation mark):

 
  
  

  
   
   
CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' insertuser ' (in name2 varchar (x), in Password2 varchar (x), in Age2 Adddate2 datetime)
  
   
   
Begin
  
   
   
DECLARE stmt varchar (2000);
  
   
   
Set @sqlstr =concat (' INSERT INTO users ' (name,password,age,adddate) values (');
  
   
   
Set @sqlstr =concat (@sqlstr, ' ", name2, ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ',
  
   
   
Prepare stmt from @sqlstr;
  
   
   
Execute stmt;
  
   
   
End