0

I have been creating a stored procedure to automate the addition of "pseudo-rows" to dimension tables (e.g. -1 = 'Unknown', -2 = 'None', etc.)

Since we have several database that contain datamart data, I opted to make this a system stored procedure that can be shared from multiple databases. The procedure queries the local database system tables (sys.tables, sys.columns, sys.schema) to construct a dynamic SQL statement that will then be executed.

However, the stored procedure is not working as intended: despite marking the procedure a system stored procedure, it is working only in the context of the Master database.

Here's a bare-bones implementation that illustrates the issue:

 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

...and my output looks like

  -----------------
  SomeOtherDatabase
  (1 row affected)

 ------------------
 master
 (1 row affected)

This is precisely NOT as expected. By marking the stored procedure as a system procedure, I expected the CALLING database context to be used, but that's not happening.

Any idea what's going on here?

0

1 Answer 1

4

Why are you using the master prefix? That is saying "go execute this procedure in the context of master."

Try just EXEC dbo.sp_WhereAmI; or even EXEC tempdb.dbo.sp_WhereAmI;...

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.