Insert into table variable with a select with variables

[TysonLPrice]

From what I've seen doing some searching the answer is no 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?

 

[jbenson001]

Can I ask why there is a necessity to change #tables to @tables?

Jim

 

[TysonLPrice]

In this case the stored procedure is very slow. One of the reasons I think is the insert in the example is embedded in a cursor loop so there is all the logging from that. Throughout the rest of the SP the data is massaged with update staements (more logging). Plus in other cases I have done the conversion I have seen performance improvements.

So maybe @Tables will speed things up.

I'm not going to try and tear this thing down and rework it. It's massive and feeds a financial report that balances to other less complicated reports (kind of a summary). I inherited it and I'm stuck with it for now.

 

[gmmastros]

I'm not making this up!

Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977

5th bullet item under A3

Also, in the same article...

In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom