高效的动态sql

一、动态sql简单介绍：

动态SQL简单的说就是将外部输入的条件内容与SQL语句形成字符串变量内容，然后执行字符串中的SQL语句，好处是灵活性大大提高，sql代码精简；坏处是效率有所降低，另外需要多注意注入漏洞。对于主流的sqlserver、oracle、mysql都有动态sql之说，这里我主要简单举例说明一下MSSQL的动态sql使用。

二、最简单的动态sql：

declare @sql varchar(200)
select @sql='select * from users'
exec(@sql)

上面的查询结果与select * from users是一样的，好像看不到有啥优势啊，而且复杂了点，不要着急，看下面的实际应用的例子。

sqlserver管理器

三、完整存储过程例子：

看完后下面例子，灵活的优势就有体会了！

/*
查询统计存储过程
exec pro_zzv_search_all 10,'','','','','','','','2017-06-26','2017-7-06','admin'
Author：zzv
Date：2017-07-05
*/
Create Proc pro_zzv_search_all
(
@ftype varchar(10), --10销售统计 11按人员统计
@fimei varchar(30), -- 串号
@fsms varchar(50) ,--短信
@foperator varchar(20), --操作人员
@fitemnumber varchar(30), -- 物料编码
@fitemname varchar(50), -- 物料名称
@fstocknumber varchar(30),--仓库编码
@fstockname varchar(50),--仓库名称
@fstartdate varchar(20),--开始日期
@fenddate varchar(20),--结束日期
@foperatorid varchar(10) --当前操作用户
)
as
declare @mysql varchar(4000),@mysql_filter varchar(3000)
select @mysql='',@mysql_filter=''
/**************根据传入参数形成过滤条件******************/
if @fitemnumber!=''
select @mysql_filter=@mysql_filter+' and fitemnumber like '''+@fitemnumber+'%'' '
if @fitemname!=''
select @mysql_filter=@mysql_filter+' and fitemname like ''%'+@fitemname+'%'' '
if @fstocknumber!=''
select @mysql_filter=@mysql_filter+' and fstocknumber like '''+@fstocknumber+'%'' '
if @fstockname!=''
select @mysql_filter=@mysql_filter+' and fstockname like ''%'+@fstockname+'%'' '
if @fimei!=''
select @mysql_filter=@mysql_filter+' and fimei like '''+@fimei+'%'' '
if @fsms!=''
select @mysql_filter=@mysql_filter+' and fsms like ''%'+@fsms+'%'' '
if @foperator!=''
select @mysql_filter=@mysql_filter+' and (foperatorname like '''+@foperator+'%'' or foperatorid like '''+@foperator+'%'' or fmobile like '''+@foperator+'%'' )'
/**************销售统计功能部分******************/
if @ftype=10
begin -----------------------------------------------------------------------------
select @mysql=@mysql+' select fareaname as 地区,fstockname as 营业厅,foperatorid as 业务员编号,foperatorname as 业务员,fimei as 串号,fdatetime as 日期,fitemnumber_kd as 机型编码,fitemname as 机型,fcompanyname as 客户,fbillnumber as 单据号,fdatetime_kd as 单据日期,''1'' as 自测 '
select @mysql=@mysql+' from T_receive_all '
select @mysql=@mysql+' where fstatus=1 and fdatetime>='''+@fstartdate+''' and fdatetime<='''+@fenddate+''' '
select @mysql=@mysql+@mysql_filter+' order by fdatetime desc'
--print @mysql
exec(@mysql)
return
end
/**************业务员统计功能部分******************/
if @ftype=11
begin
select @mysql=@mysql+' select fstocknumber as 营业厅编码,fstockname as 营业厅,convert(varchar(10),foperatorid) as 业务员编号,foperatorname as 业务员,fimei as 串号,fdatetime as 日期,fitemnumber_kd as 机型编码,fitemname as 机型,fcompanyname as 客户,fbillnumber as 单据号,fdatetime_kd as 单据日期 '
select @mysql=@mysql+' from T_receive_all '
select @mysql=@mysql+' where fstatus=1 and fdatetime>='''+@fstartdate+''' and fdatetime<='''+@fenddate+''''
select @mysql=@mysql+@mysql_filter+' order by fdatetime desc'
print @mysql
exec(@mysql)
return
end

存储过程