My first MS SQL Server Bug Report

This morning filed my first SQL Bug report: https://connect.microsoft.com/SQLServer/feedbackdetail/view/973290/temporary-stored-procedures-have-execution-context-of-these-databases-where-they-were-created

Here is the problem:  
Temporary Stored Procedures have execution context of these databases where they were created.


What actually happens:
Temporary Stored Procedures, when created, remember the database, which was current at the time of their creation and use that database as current during the execution of select statements from system tables.

In other words: if you want to get list of database objects within temporary Stored Procedure you can do it only from specified database or from the database where that procedure was created.

Not sure if I explained it clearly, but here is a simple example:

USE msdb; GO CREATE TABLE tbl_VeryTestTable_123456789ABCDE(ID INT IDENTITY(1,1), A VARCHAR(10)); GO CREATE PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE AS SELECT DB_NAME() as 'Current Database', 'Temp SP' as 'Output Source'; SELECT 'Temp SP' as 'Output Source', name FROM sys.objects WHERE name = 'tbl_VeryTestTable_123456789ABCDE'; RETURN; GO USE Master; GO EXEC #SP_VeryTemporaryProcedure_123456789ABCDE; GO USE msdb; GO DROP TABLE tbl_VeryTestTable_123456789ABCDE; GO DROP PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE; GO

What that script does:
1. Makes "msdb" database as "current".
2. Create a table in "current" "msdb" database.
3. Create temporary Stored Procedure which returns name of the "current" database and searches for just created table (in step #2) within the current database context.
4. Change current database context to "Master" database.
5. Run the new temporary Stored Procedure.
    As a result you get two output data sets:
    A. Current database: "Master"
    B. The table will be found in "current database context"!!!!! 
6. 7. 8. Return to msdb DB and cleanup after yourself.

How did I find this:
I've tried to create a temporary Stored Procedure which would return list of objects from "current" database it worked perfectly until I changed the current database. Being in different database confused me, I couldn't see objects I expected to see there and couldn't find objects which were listed by the
Stored Procedure.

I assume that bug is not very annoying (who use temp SPs at all???)  and I do not expect MS to fix it any time soon. At least we have to live with it in SQL 2012 and 2014.

So, the simple workaround would be to use dynamic SQL. 
Here is an example how to do this:

CREATE PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE AS DECLARE @SQL VARCHAR(1000); SELECT DB_NAME() as 'Current Database', 'Temp SP' as 'Output Source'; SELECT 'Temp SP' as 'Output Source', name FROM sys.objects WHERE name = 'tbl_VeryTestTable_123456789ABCDE'; SELECT @SQL = ' SELECT ''Temp SP against "'     + DB_NAME() + '" database.''  as ''Output Source'', name FROM ['     + DB_NAME() + '].sys.objects WHERE name = ''tbl_VeryTestTable_123456789ABCDE'';'; EXEC (@SQL); RETURN; GO

This Stored Procedure will query really Current database.

If you have any other Ideas please let me know.