December 16, 2005 at 5:32 am
I posted this thread at another site and did not get any meaningful responses. I'm assuming it's because the answer is no, which my own research indicates, but maybe someone here has a solution.
I want to convert a SP from using a temporary table to using a table variable. The problem is the table is currently being loaded with an EXEC. I'm not going to post the whole thing because it is huge but here are the relevant pieces:
insert into #IntermediateBR
exec('
select *
,OtherReductions = ChargeAmount
...it goes on and on until
Where 1=1 ' + @WhereClause + ')
There are variables scattered throughout the parts of the select I omitted. The @WhereClause is built from a select to table that has the conditions inserted from a Visual Basic program.
So...I need a way to insert into a table variable a select that contains variables.
Any constructive suggestions?
December 16, 2005 at 7:15 am
As stated in the link you will not be able to replace your temp table with a table variable and even if you could I doubt there would be any improvement. If there is bad performance then it will be due to the select statement(s) and not the inserts. Without seeing all the code it is difficult to suggest much, however why not create the table first, add the extra columns and then do the exec, eg
SELECT TOP 0 * INTO #IntermediateBR FROM
ALTER TABLE #IntermediateBR ADD [OtherReductions] numeric(9,2)
INSERT INTO #IntermediateBR
EXEC('SELECT *,ChargeAmount FROM
WHERE 1=1 ' + @WhereClause)
if the select is the cause of the performance then this will not help either.
In cases like this I tend to break up the query into smaller chunks and either use several temp tables or use one temp table with multiple updates.
Hope this helps.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply