sql - 逗号分隔的作业名称不会拉入作业 ID 以在 While 循环中执行，但在查询单个作业名称时工作正常

Question

由于在使用逗号分隔值运行时出现一些问题，下面的 SQL 脚本无法获取作业 ID

如果我一一查询JOB ID而不是逗号分隔，同样运行良好

dROP TABLE #J_Names
    
    dECLARE @Job_Names nvarchar(max) = 'RDP_Company_CompanyMaintenance_CompareChanges_Address,
    RDP_Company_CompanyMaintenance_CompareChanges_BusinessDescription,
    RDP_Company_CompanyMaintenance_CompareChanges_Char,
    RDP_Company_CompanyMaintenance_CompareChanges_Char_ANZSICr,
    RDP_Company_CompanyMaintenance_CompareChanges_Char_FirmCharacteristic,
    RDP_Company_CompanyMaintenance_CompareChanges_Char_NACE,
    RDP_Company_CompanyMaintenance_CompareChanges_Char_NAICS,
    RDP_Company_CompanyMaintenance_CompareChanges_Char_PrimaryFirmType,
    RDP_Company_CompanyMaintenance_CompareChanges_Char_SICFirmCharacteristic,
    RDP_Company_CompanyMaintenance_CompareChanges_CharOwnership,
    RDP_Company_CompanyMaintenance_CompareChanges_CharToDate,
    RDP_Company_CompanyMaintenance_CompareChanges_CompanyBasic,
    RDP_Company_CompanyMaintenance_CompareChanges_CompanyCount,
    RDP_Company_CompanyMaintenance_CompareChanges_CompanyExtended,
    RDP_Company_CompanyMaintenance_CompareChanges_CompanyLongBusinessDescription,
    RDP_Company_CompanyMaintenance_CompareChanges_CompanyOwnership,
    RDP_Company_CompanyMaintenance_CompareChanges_CompanyWebsite,
    RDP_Company_CompanyMaintenance_CompareChanges_CompareChanges_AlternateCompanyNameAndNativeLanguage,
    RDP_Company_CompanyMaintenance_CompareChanges_CurrentNativeCompanyName,
    RDP_Company_CompanyMaintenance_CompareChanges_FinancialMetric,
    RDP_Company_CompanyMaintenance_CompareChanges_FundInfo,
    RDP_Company_CompanyMaintenance_CompareChanges_TransactionDataDate,
    RDP_Company_CompanyMaintenance_CompareChanges_TransactionDataInteger'
    
    
    DECLARE @sql_xml XML =  CAST(('<X>'+REPLACE(@Job_Names,',' ,'</X><X>')+'</X>') AS XML)
    SELECT N.value('.', 'sysname') AS j_Name, ROW_NUMBER() over (order by N.value('.', 'sysname') ) ID
    INTO #J_Names
    FROM @sql_xml.nodes('X') AS T(N)
    
    
    Declare @countOfSteps int ;
    Declare @Job_Name sysname ;
    
    
    Select @countOfSteps = Count(*) From #J_Names
    
    While (@countOfSteps) > 0
    BEGIN
    
    
    Select top 1 @Job_Name = (select RTRIM(LTRIM(j_Name)))  From #J_Names order by ID 
    
    --SELECT  CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs WHERE (name = 'RDP_Company_CompanyMaintenance_CompareChanges_BusinessDescription')
    --SELECT CONVERT(sysname, (select RTRIM(LTRIM( @Job_Name))))
    --Select top 1 j_Name From #J_Names order by ID
    --Select top 1 (select RTRIM(LTRIM(j_Name)))  From #J_Names order by ID
    DECLARE @jobId binary(16);

SELECT @jobId = CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs WHERE (name =(select RTRIM(LTRIM(@Job_Name))) )
    
    --print @Job_Name 
    --print 'Hello' 
    --print @jobId
    --SELECT top 1 * FROM msdb.dbo.sysjobs WHERE name = N''+@Job_Name+''
    
    IF (@jobId IS NOT NULL)
    BEGIN
        EXEC msdb.dbo.sp_delete_job @jobId
    END
    
    Delete from #J_Names where j_Name = @Job_Name
    Select @countOfSteps = Count(*) From #J_Names
    
    END

对于上面的脚本，如果我手动运行下面的查询，它会给我 JOB ID，任何人都可以帮助检查逗号分隔的字符串是否不是遍历循环以获取 JOBIds 的正确方法

SELECT  CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs WHERE (name = 'RDP_Company_CompanyMaintenance_CompareChanges_BusinessDescription')

标签： sqlsql-serversql-server-2019sql-job