Eu tenho criado um procedimento armazenado para automatizar a adição de "pseudo-linhas" às tabelas de dimensão (por exemplo -1 = 'Desconhecido', -2 = 'Nenhum', etc.)
Como temos vários bancos de dados que contêm dados de datamart, optei por fazer disso um procedimento armazenado do sistema que pode ser compartilhado de vários bancos de dados. O procedimento consulta as tabelas do sistema de banco de dados local (sys.tables, sys.columns, sys.schema) para construir uma instrução SQL dinâmica que será executada.
No entanto, o procedimento armazenado não está funcionando como pretendido: apesar de marcar o procedimento como um procedimento armazenado do sistema, ele está funcionando apenas no contexto do banco de dados mestre.
Aqui está uma implementação básica que ilustra o problema:
USE Master
GO
CREATE PROCEDURE dbo.sp_WhereAmI
AS SELECT DB_NAME()
GO
EXEC sp_MS_marksystemobject 'dbo.sp_WhereAmI'
GO
USE SomeOtherDatabase
GO
SELECT DB_NAME()
EXEC MASTER.dbo.sp_WhereAmI
... e minha saída se parece com
-----------------
SomeOtherDatabase
(1 row affected)
------------------
master
(1 row affected)
Isto é precisamente NÃO como esperado. Ao marcar o procedimento armazenado como um procedimento do sistema, eu esperava que o contexto do banco de dados CALLING fosse usado, mas isso não está acontecendo.
Alguma ideia do que está acontecendo aqui?