如何将参数传递给将执行存储过程的SQL作业

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).

现在，我想将https：// localhost值传递给变量并传递给存储过程（由于某种原因，我无法在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?

有任何想法吗？

3 个解决方案

#1

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

@ReturnCode在那做什么？是在某处宣布的？

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

#2

I think you need to double quote the quotation marks

我想你需要加引号引号

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

#3

You must write any information into your command script. Jobs have their own sessions. How they will know what was your parameters

您必须将任何信息写入命令脚本。乔布斯有自己的会议。他们将如何知道你的参数是什么

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

#1