Tworzenie własnej procedury składowanej jako procedury systemowej

Często zdarza się, że gdy korzystamy z wbudowanych procedur składowanych SQL Server to czegoś nam brakuje. Zacznijmy od tego, że takie procedury składowane traktowane jako systemowe procedury SQL server charakteryzują się tym, że:

• nazwa zaczyna się od prefixu sp_
• procedury te umieszczone są w bazie master

Tworząc procedurę składowaną w systemowej bazie danych master SQL Server z prefixem sp_

• można skorzystać z algorytmu rozpoznawania nazw procedur składowanych silnika. Jeśli procedura składowana zaczyna się od sp_ , silnik najpierw przeszuka główną bazę danych master, a następnie sprawdzi bieżącą bazę danych. Dochodzimy właśnie do konkluzji, że rozpoznawanie nazw jest właśnie powodem, dla którego nigdy nie powinno się poprzedzać własnych procedur składowanych przedrostkiem sp_ , ponieważ przy każdej próbie wykonania tych procedur będziemy stale ponosić koszty związane z niepotrzebnym wyszukiwaniem i pomijaniem pamięci podręcznej.

Podczas uruchamiania procedury z prefiksem sp_ z poziomu master istnieją pewne subtelności dotyczące używanego kontekstu bazy danych, który ma wpływ na to, jakie dane są zwracane.

Utwórzmy testową procedurę składowaną

USE MASTER 
GO

IF OBJECT_ID('sp_demo') IS NOT NULL 
DROP PROCEDURE dbo.sp_demo
GO 

CREATE PROCEDURE dbo.sp_demo
AS 
   SET NOCOUNT ON 
   SELECT DB_NAME() 

   -- ANSI view 
   SELECT * 
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_NAME = 'person' 

   -- SQL Server 2005 and later table 
   SELECT * 
   FROM sys.objects 
   WHERE NAME = 'person' 

   SELECT TOP 1 * 
   FROM dbo.person
GO 

-- execute the stored procedure
USE demo_db
GO 

EXEC sp_demo
GO 

W wyniku uruchomienia takiej procedury uzyskujemy

oraz błąd:

Msg 208, Level 16, State 1, Procedure sp_demo, Line 18 [Batch Start Line 31]
Invalid object name 'dbo.person’.

Co widzimy:

• funkcja DB_NAME() działała w kontekście bazy danych demo_db
• instrukcje korzystające z INFORMATION_SCHEMAjak i sys.objects nie działają, a operacja SELECT z tabeli dbo.person całkowicie się nie powiodła.

Nie zadziałały one, ponieważ instrukcje faktycznie działały w kontekście systemowej bazy danych master. Chociaż procedurę odnaleziono w pliku master, nie było wystarczająco inteligentne użycie bieżącego kontekstu bazy danych dla określonych informacji katalogowych SQL Server 2005 i nowszych.

Rozwiązanie problemu:

Istnieje nieudokumentowana systemowa procedura składowana o nazwie sp_ms_marksystemobject , której można użyć do oznaczenia w silniku, że ​​procedura składowana powinna zostać uruchomiona tak, jakby była systemową procedurą składowaną dostarczoną przez firmę Microsoft.

Podobnie jak inne nieudokumentowane polecenia, może ono zniknąć w przyszłej wersji i należy go używać na własne ryzyko.

Uruchommy skrypt poniżej:

USE MASTER 
GO 

EXEC sp_ms_marksystemobject 'sp_demo' 
GO 

SELECT NAME
, IS_MS_SHIPPED 
FROM SYS.OBJECTS 
WHERE NAME = 'sp_demo' 

Po ponownym uruchomieniu naszej procedury w kontekście bazy demo_db uzyskujemy:

Czyli problem został rozwiązany.

• Jesteśmy w kontekście bazy demo_db,
• instrukcje korzystające z INFORMATION_SCHEMA jak i sys.objects działają prawidłowo zwracając komplet danych , a operacja SELECT z tabeli dbo.person powiodła się.

Uwaga: dodawanie obiektów do bazy master nie jest ogólnie uważane za dobrą praktykę, dlatego nie powinno się z tej funkcji korzystać na serwerach produkcyjnych.