Build temporary table with dynamic sql in SQL Server 2008

Question

To make a long story short...

I'm building a web app in which the user can select any combination of about 40 parameters. However, for one of the results they want(investment experience), I have to extract information from a different table and compare the values in six different columns(stock exp, mutual funds exp, etc) and return only the highest value of the six for that specific record.

This is not the issue. The issue is that at runtime, my query to find the investment exp doesn't necessarily know the account id. Considering a table scan would bring well over half a million clients, this is not an option. So what I'm trying to do is edit a copy of my main dynamically built query, but instead of returning 30+ columns, it'll just return 2, the accountid and experienceid (which is the PK for the experience table) so I can do the filtering deal.

Some of you may define dynamic sql a little different than myself. My query is a string that depending on the arguments sent to my procedure, portions of the where clause will be turned on or off by switches. In the end I execute, it's all done on the server side, all the web app does is send an array of arguments to my proc.

my over simplified code looks essentially like this:

declare @sql varchar(8000)
set @sql = 
'select [columns]
into #tempTable
from [table]
[table joins]' + @dynamicallyBuiltWhereClause

exec(@sql)

after this part I try to use #tempTable for the investment experience filtering process, but i get an error telling me #tempTable doesn't exist.

Any and all help would be greatly appreciated.

Answer 1

The problem is the scope of your temp table only exists within the exec() statement. You can transform your temp table into a "global" temp table by using 2 hash signs -> ##tempTable. However, I wonder why you are using a variable @dynamicallyBuiltWhereClause to generate your SQL statement.

I have done what you are doing in the past, but have had better success generating SQL from the application (using C# to generate my SQL).

Also, you may want to look into Table Variables. I have seen some strange instances using temp tables where an application re-uses a connection and the temp table from the last query is still there.