Call stored procedure in separate session in SQL Server

I tried calling a SQL Server stored procedure in another session.

This is my code:

CREATE PROCEDURE [dbo].[CI_AdHoc_PrepareJob]
(
    @JobName NVARCHAR(50),
    @StepName NVARCHAR(50),
    @Value NVARCHAR(MAX)
)
AS
BEGIN
    BEGIN TRAN;

    DECLARE @MyStepName SYSNAME = @StepName + '_' + CAST(NEWID() AS NVARCHAR(36));
    DECLARE @MyCmd NVARCHAR(MAX) = 'EXEC CI_AdHoc_SP @id=1, @value=''' + @Value + '''';

    -- Call helper to create the job step immediately
  EXEC dbo.sp_add_jobstep
    @jobname = @JobName,
    @stepname = @myStepName,
    @subsystem = N'TSQL',
    @command = @MyCmd,
    @on_success_action = 1, -- Quit with success
    @on_fail_action = 2, -- Quit with failure
    @DatabaseName = 'MyDB';


    -- Now start the job
    EXEC msdb.dbo.sp_start_job 
        @job_name = @JobName,
        @step_name = @MyStepName;

    -- Get Job ID (optional)
    DECLARE @JobId UNIQUEIDENTIFIER;
    SELECT @JobId = job_id FROM msdb.dbo.sysjobs WHERE name = @JobName;

    ROLLBACK TRAN;
END

I want that even I do rollback at the end, the stored procedure will run (without the rollback interfere it. It is used for some updates in db i.e.).

How can I do that?

Does running job with parameter - a good solution.

(It may be fine whether there are no parameters for the stored procedure, but I need passing parameter, so I need to call sp_add_jobstep inside the transaction. I didn't find any way doing that otherwise).

There is a solution for that.

Indeed jobs are run on separated session.

The problem, that you cannot pass parameters to the stored procedure (while they are kept in other session). i.e. whether you add in a temporary table and do rollback at the end.

• Save the parameters in a temporary file.

• Add a stored procedure, that can add data into the temporary file and call to the job.

• Add a stored procedure that the job can call (i.e. the latest file found).

  • In that stored procedure - run sql with the parameters read from the file.

For that solution - any call to the special stored procedure (that calls the job) won't be interfered by any transactions.