Shortly speaking...
I am creating a web application in which the user can select any combination of approximately 40 parameters. However, for one of the results they want (investment experience), I have to extract information from another table and compare the values in six different columns (stock exp, mutual funds exp, etc.) and return only the highest value of six for this specific record.
It's not a problem. The problem is that at runtime, my exp investment search request does not necessarily know the account id. Given that scanning the table has brought more than half a million customers, this is not an option. So I'm trying to edit a copy of my main dynamically constructed query, but instead of returning 30+ columns, it just returns 2, accountid and experienceid (this is PK for the experience table), so I can do the filtering deal.
Some of you may define dynamic SQL a little differently than me. My query is a string that, depending on the arguments sent to my procedure, parts of the where clause will be turned on or off by switches. In the end, I execute, all this is done on the server side, the entire web application sends an array of arguments to my proc.
My more simplified code looks something 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 filtering process of the investment experience, but I get an error message indicating that #tempTable does not exist.
Any help is appreciated.
source
share