How to pass parameter to SQL job that will execute stored procedure

Question

I have the code below (only the part 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 to a variable and go 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 does not work. I am also searching the net for any idea / syntax, etc., but so far no luck.

Any ideas?

Answer 1

what does @ReturnCode do @ReturnCode ? is it announced somewhere?

I tried this on a new assignment 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 

Answer 2

I think you need to specify quotation marks twice

 SET @domainName ='https://localhost:' SET @sp ='exec [dbo].[PORT_Insert_Record] ''' + @domainName + '''' 

Answer 3

You must write any information to your script command. Workers have their own sessions. How do they know what your parameters were.

Here is an example

 --here is your existing parameters declare @dateparam date = '2017-10-19' declare @intparam int= 100 declare @stringparam nvarchar(20)= 'hello' --now start write you sql job command declare @command nvarchar(max) set @command = N'declare @dateparam date =' + CAST(@dateparam AS NVARCHAR(20)) + CHAR(13) --+ CHAR(13) IS FOR LINEBREAK set @command = @command + N'declare @intparam date =' + CAST(@intparam AS NVARCHAR(20)) + CHAR(13) set @command = @command + N'declare @stringparam nvarchar(20) =' + @stringparam + CHAR(13) set @command = @command + N'exec dbo.my_store_procedure @dateparam, @intparam, @stringparam' --NOW YOUR COMMAND HAVE ALL informations 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=@command , @database_name=N'master', @flags=0