一个简单的存储过程范例

delimiter $$
CREATE PROCEDURE `proc_Ranking`(IN sku VARCHAR(20),IN skuname VARCHAR(20),IN status VARCHAR(20),IN sales VARCHAR(20),IN today VARCHAR(20),IN old_time VARCHAR(20))
BEGIN
    SET @sku = sku;
    SET @skuname = skuname;
    SET @status = status;
    SET @sales = sales;
    SET @today = today;
    SET @old_time = old_time;
    SET @sql_header = "SELECT
             c.`sku`,c.`amt`,c.`avgs` avg,s.`skuname`,s.`skupic`,s.`edittime`,st.`statusname`
        FROM(
            SELECT
                 `sku` , ROUND(SUM( `amt` ) , 2) 'amt' , ROUND(SUM( `avg` ) /7 , 2) 'avgs'
            FROM `skusales`
            WHERE    
                 `sku` <> ''
            AND `paytime` > @old_time
            AND `paytime` < @today
            GROUP BY `sku`) c
        　　　　LEFT JOIN
            　　　　`skustatus` s ON c.`sku` = s.`sku`
    　　　　　　　　LEFT JOIN
        　　　　　　　　`statustype` st ON s.`sale_status` = st.`pid`
        WHERE
            1";
    IF @sku <> '' THEN
    SET @sql_header = CONCAT(@sql_header," AND c.`sku` = @sku");
    END IF;

    IF @status <> '' THEN
    SET @sql_header = CONCAT(@sql_header," AND s.`sale_status` = @status");
    END IF;

    IF @skuname <> '' THEN
    SET @sql_header = CONCAT(@sql_header," AND s.`skuname` like @skuname");
    END IF;

    IF @sales <> '' THEN
    SET @sql_header = CONCAT(@sql_header," AND c.avgs");
    SET @sql_header = CONCAT(@sql_header,@sales);
    END IF;
    SET @sql_header = CONCAT(@sql_header," ORDER BY c.amt DESC");

    SET @sql_bodys = @sql_header;
    
    PREPARE stmt FROM @sql_bodys;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt ;
        
    END$$