mysql存储过程中根据传入参数，动态拼sql语句并且执行

code-jam

于 2017-10-20 15:40:48 发布

阅读量9.1k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql存储过程动态拼接sql语句 mysql存储过程进阶 mysql存储过程动态参数

本文链接：https://blog.csdn.net/festone000/article/details/78296189

数据库专栏收录该内容

53 篇文章

订阅专栏

CREATE DEFINER = 'root'@'%'
PROCEDURE databaseTest.myProcTest(IN i_start_index INT, IN i_capacity INT, IN i_type INT, IN i_category INT)
BEGIN
    IF i_type = 0 THEN
      set @sqlType= '';
    ELSEIF i_type = 1 THEN
        set @sqlType= ' AND hsr.type = 1';
    END IF;

    IF i_category = 0 THEN
      set @sqlCategory = '';
    ELSE
      set @sqlCategory = CONCAT(' AND hsr.category_type = ', i_category);
    END IF;

  set @sql0 = 'SELECT            	
                *
              	FROM
                table_h_s_r hsr
                WHERE hsr.auction_status = 1 ';
      
  set @sql1 = CONCAT(' order BY hsr.sort_index asc LIMIT ',i_start_index, ' , ' , i_capacity);     

  set @finalSql = CONCAT(@sql0,@sqlType,@sqlCategory,@sql1);
  
  PREPARE stmt FROM @finalSql;

  EXECUTE stmt;
   
END