MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

类似于SQL Server中的:sp_executesql

sql server script:

--- 涂聚文 20160906IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuDeptUserCount')DROP PROCEDURE proc_Select_DuDeptUserCountGOCREATE PROCEDURE proc_Select_DuDeptUserCount( @where NVARCHAR(1000))ASDECLARE @sql NVARCHAR(4000)SET @sql='select count(*) as H from DuDeptUser 'IF @where<>'' SET @sql=@sql+@whereEXEC(@sql)GO

MySql script:

# 建表 塗聚文 20160907drop table attendrecord;create table attendrecord(    seq  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,    emp_no varchar(20) null,    rdate datetime not null,    rtime time not null,    rdescription varchar(100),    rdes_reasnon varchar(100),    branch varchar(50));# 添加DELIMITER $$DROP PROCEDURE  IF EXISTS  `attend`.`proc_Insert_Attendrecord` $$CREATE PROCEDURE `attend`.`proc_Insert_Attendrecord`(IN param1emp_no VarChar(20),IN param1rdate Datetime ,IN param1rtime Time,IN param1rdescription VarChar(100),IN param1rdes_reasnon VarChar(100),IN param1branch VarChar(50))BEGININSERT INTO attendrecord(emp_no ,rdate ,rtime ,rdescription ,rdes_reasnon ,branch)VALUES(param1emp_no ,param1rdate ,param1rtime ,param1rdescription ,param1rdes_reasnon ,param1branch); END $$DELIMITER ;-- 添加DELIMITER $$DROP PROCEDURE  IF EXISTS  proc_Insert_Attendrecord $$CREATE PROCEDURE proc_Insert_Attendrecord(IN param1emp_no VarChar(20),IN param1rdate Datetime ,IN param1rtime Time,IN param1rdescription VarChar(100),IN param1rdes_reasnon VarChar(100),IN param1branch VarChar(50))BEGININSERT INTO attendrecord(emp_no ,rdate ,rtime ,rdescription ,rdes_reasnon ,branch)VALUES(param1emp_no ,param1rdate ,param1rtime ,param1rdescription ,param1rdes_reasnon ,param1branch); END $$DELIMITER ;#添加DELIMITER $$DROP PROCEDURE IF EXISTS proc_Insert_AttendrecordOutput $$CREATE PROCEDURE proc_Insert_AttendrecordOutput(IN param1emp_no VarChar(20),IN param1rdate Datetime,IN param1rtime Time,IN param1rdescription VarChar(100),IN param1rdes_reasnon VarChar(100),IN param1branch VarChar(50), out param1seq int)BEGININSERT INTO attendrecord(emp_no ,rdate ,rtime ,rdescription ,rdes_reasnon ,branch)VALUES(param1emp_no ,param1rdate ,param1rtime ,param1rdescription ,param1rdes_reasnon ,param1branch);SELECT LAST_INSERT_ID() into param1seq;END $$DELIMITER ;#更新 塗聚文註 2016-09-07DELIMITER $$DROP PROCEDURE IF EXISTS proc_Update_Attendrecord $$CREATE PROCEDURE proc_Update_Attendrecord(IN param1seq Int(8),IN param1emp_no VarChar(20),IN param1rdate Datetime,IN param1rtime Time,IN param1rdescription VarChar(100),IN param1rdes_reasnon VarChar(100),IN param1branch VarChar(50))BEGINUPDATE attendrecordSETemp_no=param1emp_no ,rdate=param1rdate ,rtime=param1rtime ,rdescription=param1rdescription ,rdes_reasnon=param1rdes_reasnon ,branch=param1branchwhereseq=param1seq;END $$DELIMITER ;#修改某一列数据DELIMITER $$DROP PROCEDURE IF EXISTS proc_Update_AttendrecordField $$CREATE PROCEDURE proc_Update_AttendrecordField(IN param1seq Int(8),IN FieldValue VarChar(200))BEGINDECLARE sqlstr varchar(2000);DECLARE wherestr varchar(1000);SET sqlstr=CONCAT('update  attendrecord set ',FieldValue);SET wherestr=CONCAT(' WHERE seq =',cast(param1seq as char(20)));SET sqlstr=CONCAT(sqlstr,wherestr);SET @Sql=sqlstr;PREPARE stmt FROM @Sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END $$DELIMITER ;# 測試call proc_Update_AttendrecordField(1,'rdes_reasnon=''geovin''');-- 刪除DELIMITER $$DROP PROCEDURE IF EXISTS proc_Delete_Attendrecord $$CREATE PROCEDURE proc_Delete_Attendrecord(IN param1seq Int)BEGINDELETE fromattendrecordWHEREseq = param1seq;END $$DELIMITER ;#删除多条记录DELIMITER $$DROP PROCEDURE IF EXISTS proc_Delete_AttendrecordId $$CREATE PROCEDURE proc_Delete_AttendrecordId(IN param1seq varchar(1000))BEGINDELETE fromattendrecordWHEREseq IN(param1seq);END $$DELIMITER ;#查詢DELIMITER $$DROP PROCEDURE IF EXISTS proc_Select_Attendrecord $$CREATE PROCEDURE proc_Select_Attendrecord(IN param1seq Int)BEGINSELECT * FROM attendrecord WHERE seq = param1seq;END $$DELIMITER ;#是否在存在DELIMITER $$DROP PROCEDURE IF EXISTS proc_Select_AttendrecordExists $$CREATE PROCEDURE proc_Select_AttendrecordExists(IN param1seq Int)BEGINSELECT count(1) as H FROM attendrecord WHERE seq = param1seq;END $$DELIMITER ;-- 测试call proc_Select_AttendrecordExists(1);#表有多少条记录 Geovin DuDELIMITER $$DROP PROCEDURE IF EXISTS proc_Select_AttendrecordCount $$CREATE PROCEDURE proc_Select_AttendrecordCount(IN wherestr varchar(1000))BEGINDECLARE sqlstr varchar(2000);SET sqlstr='SELECT count(1) as H FROM attendrecord';if wherestr='' thenSET sqlstr=sqlstr;elseSET sqlstr=sqlstr+wherestr;end if;SET @sqlstr=sqlstr;PREPARE stmt FROM @sqlstr;EXECUTE stmt;DEALLOCATE PREPARE stmt;END $$DELIMITER ;# 测试call proc_Select_AttendrecordCount('');# http://stackoverflow.com/questions/23545525/mysql-stored-procedure-prepared-statement-dynamic-sql-parameterized# https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html  -- 测试  set @sql='SELECT count(1) as H FROM attendrecord';  set @where='WHERE seq =1';  set @sql=@sql+@where;  select @sql;    # testset @sql='SELECT * FROM attendrecord';set @where=' WHERE seq <>1';set @sql=CONCAT(@sql,@where);select @sql;  #显示字符串PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;  -- 测试--SET @sql = "select * from attendrecord";PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;-- 测试SET @skip=1; SET @numrows=5;SET @Sql='SELECT * FROM tbl attendrecord  limit ?,?';PREPARE STMT FROM @Sql;-- EXECUTE STMT;EXECUTE STMT USING @skip, @numrows;DEALLOCATE PREPARE STMT;#视图有多少条记录DELIMITER $$DROP PROCEDURE IF EXISTS proc_Select_AttendrecordCountView $$CREATE PROCEDURE proc_Select_AttendrecordCountView(  IN wherestr varchar(1000))BEGINdeclare sqlstr varchar(2000);set sqlstr='SELECT count(1) as H FROM View_attendrecord';if wherestr='' thenset sqlstr=sqlstr;elseset sqlstr=sqlstr+wherestr;end if;set @sqlstr=sqlstr;-- call(sqlstr);PREPARE stmt FROM @sqlstr;EXECUTE stmt;DEALLOCATE PREPARE stmt;END $$DELIMITER ;-- 5.1 up#查询某记录的字段DELIMITER $$DROP PROCEDURE IF EXISTS proc_Select_AttendrecordTitle $$CREATE PROCEDURE proc_Select_AttendrecordTitle(  IN FieldName varchar(1000),  IN param1id int)BEGINdeclare sqlstr varchar(2000);declare wherestr varchar(1000);set sqlstr=CONCAT('select ',FieldName);set wherestr=CONCAT(' from attendrecord WHERE seq =',cast(param1id as char(20)));set sqlstr=CONCAT(sqlstr,wherestr);set @sqlstr=sqlstr;PREPARE stmt FROM @sqlstr;  EXECUTE stmt;DEALLOCATE PREPARE stmt;END $$DELIMITER ;call proc_Select_AttendrecordTitle('rdescription',1);select concat(2);select cast(2 as char(20));#模糊查询DELIMITER $$DROP PROCEDURE IF EXISTS proc_Select_AttendrecordFuzzySearch $$CREATE PROCEDURE proc_Select_AttendrecordFuzzySearch(  IN FieldList varchar(1000),  IN wherestr varchar(2000) )BEGINdeclare sqlstr varchar(2000);declare ifwherestr varchar(1000);declare iflist varchar(1000);set ifwherestr='';if FieldList='' then   set iflist=' * ';else   set iflist=FieldList;end if;if wherestr<>'' then   set ifwherestr=CONCAT(' WHERE ',wherestr);end if;set sqlstr=CONCAT('select ',iflist);set sqlstr=CONCAT(sqlstr,'  from attendrecord ');set sqlstr=CONCAT(sqlstr,ifwherestr);set @sqlstr=sqlstr;-- select @sqlstr;-- call(sqlstr); PREPARE stmt FROM @sqlstr; EXECUTE stmt; DEALLOCATE PREPARE stmt;END $$DELIMITER ;-- seq =1call proc_Select_AttendrecordFuzzySearch('rdescription','seq=1');#查詢所有DELIMITER $$DROP PROCEDURE IF EXISTS proc_Select_AttendrecordAll $$CREATE PROCEDURE proc_Select_AttendrecordAll()BEGINSELECT * FROM attendrecord;END $$DELIMITER ;