存储过程中拼写sql并执行

直接上代码吧，根据不同的条件拼写sql后并执行

ALTER PROCEDURE [dbo].[usp_Statistic_WJB_DZSK_ZT]
(
    @year int,
    @half int,--0表示全年,1表示上半年,2下半年
    @isAll int --0表示只统计党政干部人员，1表示统计全部
)
as
declare @sql    nvarchar(4000)

set @sql=N'select id from 
chgrwpj
where id in(select distinct parentid from chgrymd'
if(@isAll=0)
set @sql=@sql + N' where ((unitcode between ''A0000'' and ''A0025'') or (unitcode between ''B0041'' and ''B0044'') or unitcode=''A1206'' or unitcode=''B0003'' or unitcode=''B0017'' or unitcode=''B0034'' or (unitcode=''B0045'' and positionLevel<=6 and positionLevel>0 and positionname<>''验船师''))'
set @sql=@sql + N') and innerAmount<chgrsh and year(enddate)=@year'
if(@half=1)
set @sql=@sql + N' and month(enddate) between 1 and 6 '
else if(@half=2)
set @sql=@sql + N' and month(enddate) between 7 and 12'

set @sql=@sql + N' order by senddate asc'
exec sp_executesql @sql,
N'@year int,@half int',@year,@half

RETURN

 

转载于:https://www.cnblogs.com/zhengwei-cq/p/10396310.html