MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

最新推荐文章于 2024-04-18 15:56:30 发布

原创最新推荐文章于 2024-04-18 15:56:30 发布 · 873 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySql #sql server

数据库编程专栏收录该内容

37 篇文章

订阅专栏

本文介绍了一种在SQL Server及MySQL中实现动态SQL查询的方法，通过存储过程接收参数并拼接查询条件，确保了代码的灵活性与复用性。

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