sqlsql-servert-sqltokensql-job

How do I pass the SQL Server Job name to a stored procedure inside this job?


I have a job:

RunProcedures

It has steps:

Step 1: Do Something
Step 2: Do something Else
Step 3: Email

In step 3, I have:

EXEC spSendSuccessEmail -- and here's where I want to pass the job name.

Here's the code for the stored procedure above:

ALTER PROCEDURE [dbo].[spSendSuccessEmail] @JobName VARCHAR(30)
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @EmailBody VARCHAR(50)

    SET @EmailBody = @JobName + ' ran successfully'

    BEGIN TRY
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'DBTeam',
            @recipients = 'user@universe.com',
            @copy_recipients = 'user2@universe.com',
            @subject = 'Process Complete',
            @body = @EmailBody,
            @importance = 'Normal',
            @sensitivity = 'Normal';
    END TRY
    BEGIN CATCH
        EXEC spGetDatabaseErrorInfo
    END CATCH           
END

How do I pass to this stored procedure the name of the job it's in?

I read on tokens but I'm a little confused on how to accomplish this task.

Can anyone give me a hand?


Solution

  • UPDATE I did some testing and for some versions of SQL Server(2005 sp1 and beyond), you'll have to escape the Token. I've added the escape command to the code below.

    $(JOBID) is the token you want to use, and you can query from msdb..sysjobs to find the job name.

    For sql server 2005 sp1 and beyond:

    declare @jobName varchar(100)
    
    select @jobName = name from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID))
    
    exec spSendSuccessEamil @jobName
    

    For sql server 2005 and earlier:

    declare @jobName varchar(100)
    
    select @jobName = name from msdb..sysjobs where job_id = $(JOBID)
    
    exec spSendSuccessEamil @jobName
    

    Just use one of these as your command text within the 3rd step of your job.