I run a sql statement in a stored procedure like so:
...
CREATE PROCEDURE my_proc
AS
BEGIN
BEGIN TRAN
...do some work
EXEC msdb.dbo.sp_start_job N'MyJob';
...do some work, maybe rollback if some criteria is not met
COMMIT TRAN
...
END
Are the steps in 'MyJob' a part of this transaction? Or do they occur independently of it?
For a specific example: 'MyJob' deletes records from table 'MyData'. Then some logic in the stored proc ends up rolling back the transaction instead of committing it. Will the records in 'MyData' that were deleted by the job be deleted?