mysql存储过程

 

DELIMITER $$

USE `ybl_ins_test`$$

DROP PROCEDURE IF EXISTS `p_getCarData`$$

CREATE DEFINER=`carinfo_test`@`%` PROCEDURE `p_getCarData`(st INT,tk INT)
BEGIN
DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table(SELECT licenseno FROM `cardata`  WHERE STATUS=st AND licenseno IS NOT NULL AND licenseno!='' ORDER BY LastGetTime ASC LIMIT tk);

UPDATE `cardata` c,tmp_table t SET c.lastgettime=NOW() WHERE c.licenseno=t.licenseno;
SELECT * FROM tmp_table;
END$$

DELIMITER ;

 

CALL p_getCarData(0,2)

 

MySql.Data.MySqlClient.MySqlParameter parameter1 = new MySql.Data.MySqlClient.MySqlParameter("@st", request.Status);
            MySql.Data.MySqlClient.MySqlParameter parameter2 = new MySql.Data.MySqlClient.MySqlParameter("@tk", request.TakeCount);
            var result = insuredContext.Database.SqlQuery<string>("CALL p_getCarData(@st,@tk)",
                new MySql.Data.MySqlClient.MySqlParameter[]{
                        parameter1,parameter2
                }).ToList();