sql-server - SQL 查询迭代计划作业并将其删除。存储过程将 Job_Name 作为输入参数

Question

我正在解决我得到 job_id 的问题和作业名称并使用存储过程删除预定作业。这里我需要将作业名称作为输入参数传递。

我正在使用

select job_id, name from msdb.dbo.sysjobs

得到这个:

<表类="s-表"><头>job_id名字<正文>华语华语usp_1fjoajffusp_2

我想按名称而不是 job_id 删除存储过程因为它变得有点复杂。

我希望存储过程采用job_name作为参数但返回 Job_id这样我就可以使用

来执行它

EXEC msdb.dbo.sp_delete_job @job_id = N'@JobId', @delete_unused_schedule = 1

这是我的尝试:

DECLARE @JobName nvarchar(MAX)
DECLARE @JobId NVARCHAR(MAX)

DECLARE Job_ID, Job_Name CURSOR FOR
    SELECT job_id, name 
    FROM msdb.dbo.sysjobs;

OPEN Job_ID, Job_Name;

FETCH NEXT FROM Job_ID, Job_Name INTO @JobId, @JobName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_id = N'@JobId', @delete_unused_schedule = 1

    FETCH NEXT FROM Job_ID INTO @JobId;
END

CLOSE Job_ID;
DEALLOCATE Job_ID;

我认为我做错了什么，因为我遇到了语法错误。请建议我一些改进。 TIA。

Answer 1

您需要为您的游标提供一个有效名称 - 而不是列列表....

试试这段代码:

DECLARE @JobName sysname, @JobId uniqueidentifier;

-- give your cursor a proper NAME
DECLARE JobIdNameCursor CURSOR FOR
    SELECT job_id, name 
    FROM msdb.dbo.sysjobs;

-- open the cursor by NAME    
OPEN JobIdNameCursor;

-- fetch from cursor by NAME
FETCH NEXT FROM JobIdNameCursor INTO @JobId, @JobName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_id = N'@JobId', @delete_unused_schedule = 1;

    FETCH NEXT FROM JobIdNameCursor INTO @JobId, @JobName;
END

-- close and deallocate that cursor - again by its proper NAME
CLOSE JobIdNameCursor;
DEALLOCATE JobIdNameCursor;