SQL SERVER 中sp_executesql 参数说明

-- sp_executesql中参数值，要么顺序和参数申明中的顺序一致，要么指定参数名字

declare @stmt nvarchar(500)
declare @cnt  int ;
declare @maxid int

set @stmt = 'select @maxid= cast(max(userid) as int),@count = count(1) from c_user';

-- 1、按声明顺序
execute sp_executesql @stmt,N'@maxid varchar(10) output,@count int output',@maxid output,@cnt output

--2、给定参数名：
execute sp_executesql @stmt,N'@maxid varchar(10) output,@count int output',@count=@cnt output,@maxid=@maxid output

select @cnt as cnt,@maxid as maxid

 

 

示例

declare @rq1 datetime = '2024-07-01'
declare @rq2 datetime = '2024-07-31'
declare @busno varchar(10) = ''
declare @sql nvarchar(4000)=''

set @sql = '
    select count(1)
    from u_sale_c
    where accdate >= @rq1 and accdate < dateadd(day,1,@rq2)
'
if @busno <> ''
    set @sql += '
     and busno = '''+@busno+''''

execute sp_executesql @sql,N'@rq1 datetime,@rq2 datetime',@rq1,@rq2

--select count(1) from u_sale_c where accdate >='2024-07-01' and accdate < '2024-08-01'    --242758
--select count(1) from u_sale_c where accdate >='2024-07-01' and accdate < '2024-08-01' and busno ='0001'    --6441