In which context does a procedure run when called from another context?

Question

Envisage:

use databaseA;
go

exec databaseB.dbo.spProcedure();
go

Does the procedure execute against databaseA - the context from which it is called, or databaseB - the context in which it exists?

If the procedure had

insert into myTable(example) values ('example');

... and if both databases had identical tables myTable, which database will receive the new record?

Answer 1

It executes in the context of the database the object exists in. Example code:

CREATE DATABASE Temp1
GO
USE [temp1]
GO
CREATE TABLE Data1 (Val VARCHAR(255))
GO
INSERT INTO Temp1.dbo.Data1 (Val)
VALUES ('Test1')
GO
CREATE PROCEDURE spTemp1
AS
BEGIN
    SELECT * FROM dbo.Data1
END
GO


CREATE DATABASE Temp2
GO
USE [temp2]
GO
CREATE TABLE Data1 (Val VARCHAR(255))
GO
INSERT INTO Temp2.dbo.Data1 (Val)
VALUES ('Test2')
GO
CREATE PROCEDURE spTemp2
AS
BEGIN
    SELECT * FROM dbo.Data1
END
GO

USE [Temp1]
GO
EXEC Temp2.dbo.spTemp2
GO

USE [master]
GO
DROP DATABASE Temp1
DROP DATABASE Temp2
GO

This highlights that even though we query "dbo.Data1" which exists in both databases, the value returned is from that table in the Temp2 database because that is the context of the stored procedure.

The example is a SELECT, but the same holds true for an INSERT.