MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

类似于SQL Server中的:sp_executesql

sql server script:

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

MySql script:

#表有多少条记录 Geovin Du
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordCount $$
CREATE PROCEDURE proc_Select_AttendrecordCount
(
	IN wherestr varchar(1000)
)
BEGIN
declare sqlstr varchar(2000);
set sqlstr='SELECT count(1) as H FROM attendrecord';
if wherestr='' then
set sqlstr=sqlstr;
else
set sqlstr=sqlstr+wherestr;
end if;
set @sqlstr=sqlstr;
-- call(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;

# test
set @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;