27,579
社区成员
发帖
与我相关
我的任务
分享
看如下代码:
--定义参数
declare @sche_id varchar(15)
declare @dept_id varchar(15)
declare @delayDateTime datetime
--参数初始化
set @sche_id = '01'
set @dept_id = '001'
declare @delayDateTime = '2009-03-04 12:30:00'
--截取时间
declare @date varchar(15)
declare @time varchar(15)
set @time = REPLACE(convert(varchar,@delayDateTime,108),':','')
--只能为将来安排日程表
if datediff(dd,@delayDateTime,getdate()) > 0
set @delayDateTime = getdate()
set @date = convert(varchar,@delayDateTime,112)
--开始安排计划
EXEC msdb.dbo.sp_add_job
@job_name = @dept_id
EXEC msdb.dbo.sp_add_jobstep
@job_name = @dept_id,
@step_name = @sche_id,
@subsystem = 'TSQL',
--问题:怎么为存储过程传递参数,例如我想这样做:exec dbo.checkAbsence @dept_id
@command = 'exec dbo.checkAbsence',
@retry_attempts = 5,
@retry_interval = 5
exec msdb.dbo.sp_add_schedule
@schedule_name = @sche_id,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 0,
@active_start_date = @date,
@active_start_time = @time
exec msdb.dbo.sp_attach_schedule
@job_name = @dept_id,
@schedule_name = @sche_id
exec msdb.dbo.sp_add_jobserver
@job_name = @dept_id
end
--执行的存储过程
create proc dbo.checkAbsence
@dept_id
as
--任何操作
go
怎么在计划执行的时候为存储过程传递参数
输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中?
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
@command = 'exec dbo.checkAbsence @dept_id',