高效的动态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

存储过程