Using database project and SQL Data Tools in Visual Studio 2012 : How do I get temp tables to resolve?

Question

I am learning Visual Studio 2012's "database project" system, using Visual Studio 2012 with Update 1, plus SSDT.

I am finding it very good at finding real problems in my database, especially programming errors in stored procedures where someone has removed a field from a database table but didn't go through and verify that all the stored procedures execute without errors. So validation of your .sql scripts via the "build" command in Visual Studio 2012 is very handy. I would hate to abandon it.

But I also note that whenever a #TEMPTABLE is used in a stored procedure, even when "Enable Extended Transact-SQL verification for common objects" is turned off, I still get "build errors" involving #temptable.field references in stored procedures.

What steps does the Database Project take to determine the schema of a temporary table? Since my temporary tables by definition don't exist in the main schema, they didn't get into my Database Project when I imported a real production SQL database into Visual Studio via the Import Database option, right after creating the database.

Should I be creating "#TEMPTABLE.SQL" files and adding them to my project?

Sample error:

c:\dev\...\dbo\Stored Procedures\xyz.sql(95,96): Warning:  SQL71502: Procedure: [dbo].[proc123] has an unresolved reference to object [#temptable1].[somefield1].

If there was a way to include a script that defined the temptables in use once, and include it into the various places where it's necessary to know about these if the T-SQL is to be thoroughly validated, that would be fine, and if Turning of Extended Verification did what I think it's supposed to do, then perhaps nothing would be necessary.

Forum post suggests this isn't possible to fix and that all I can do is effectively turn off this warning at a file level, which is kind of horrible.

A question on this same subject but for Visual Studio 2010 suggests that this is an area where this technology has been just flat out broken and Microsoft has known for years about it and done nothing about it. Is anything better now in VS2012.U1+SSDT_Dec2012?

Animosity

Are you referring to a temp table created within the scope of its session and then used elsewhere (created in 1 proc, then used in another)? Or is this all in one proc? – Willful

These are temp tables used as almost "invisible inputs" to the stored procedure, created before this stored procedure is invoked, and only modified by the stored procedures that are invoked. That might be a pretty nasty "schema smell" in fact, since it's hard to say exactly what this stored procedure expects to have exist as a pre-state. I don't like it very much, but it's what I've got to work inside of. What's even worse is that the applications that create the temp tables are the only place right now that the schema is defined for the temporary table. I was looking for a --#pragma or... – Animosity

I asked the same question at the Microsoft social.msdn site here: social.msdn.microsoft.com/Forums/en-US/ssdt/thread/… – Animosity

any chance you could change to pass around a table-valued-type instead of the "invisible temp table input"? – Willful

Not really. I am not at liberty to modify all the non-SQL-schema areas of the system (native code that generates SQL temp tables as part of it's internal coding structure, that happens to match the ones used by the stored procedure, it becomes a really huge violation of db schema encapsulation). – Animosity

Answer 1

Thats a problem with temp table, you should use table valued functions.

Mother

This is correct. And probably no way Microsoft could safely implement a temp-table system. – Animosity