How to pass a parameter to a SQL Job that will execute a stored procedure

Question

I have the below code (only the portion that is needed)

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SomeStep', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec [dbo].[PORT_Insert_Record] ''https://localhost''',  
        @database_name=N'MyDatabase', 
        @flags=0

Now, I want to pass the https://localhost value into a variable and pass to the stored procedure (for some reason I cannot pass it inside the SP).

So I tried

DECLARE @domainName varchar(max)
DECLARE @sp varchar(max)
SET @domainName ='https://localhost:'
SET @sp ='exec [dbo].[PORT_Insert_Record]' + @domainName

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'InsertRecordIntoResellerOpportunities', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=@sp,  
        @database_name=N'MyDatabase',  
        @flags=0

but it is not working. I also search in the net for any idea/syntax etc.. but no luck as of now.

Any ideas?

Answer 1

what's that @ReturnCode doing there? is it declared somewhere?

I tried this on a new job and it worked:

DECLARE @domainName varchar(max)
DECLARE @sp varchar(max)
SET @domainName ='https://localhost:'
SET @sp ='exec [dbo].[PORT_Insert_Record]' + @domainName

    EXEC msdb.dbo.sp_add_jobstep @job_id=N'a756bfcb-2abf-4d7a-a871-85e234e7ef53', @step_name=N'Step 1', 
            @step_id=1, 
            @cmdexec_success_code=0, 
            @on_success_action=1, 
            @on_fail_action=2, 
            @retry_attempts=0, 
            @retry_interval=0, 
            @os_run_priority=0, @subsystem=N'TSQL', 
            @command=@sp, 
            @database_name=N'master', 
            @flags=0
    GO