类似于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;
