April 3, 2002 at 1:13 pm
Hi, I am trying to construct a dynamic sql containing a table varaible as this:
declare @mv_table table (t_id int, item int )
declare @mv_sql varchar(1000)
insert into @mv_table values ( 1, 1 )
set @mv_sql = 'select count(t_id) from ' + '@mv_table'
exec ( @mv_sql )
Then, I got an error:
Must declare the variable '@mv_table'.
Can only one let me know if sql can contain a table variable or not. OR, if I did something wrong?
By the way, the reason I need to do this dynamic sql is that I need to put data from 4-6 table variables into one table variable.
But I can't use union in my case.
Thanks.
Abby Zhang
April 3, 2002 at 4:48 pm
That is because the variable does not exist in the same scope as the dynamic SQL query. And then this comes from BOL
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements.
If I am right your should be able to do
declare @mv_table table (t_id int, item int )
declare @mv_sql varchar(1000)
insert into @mv_table values ( 1, 1 )
select count(t_id) from @mv_table
But sorry I have not used these as of yet or tested.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 3, 2002 at 5:43 pm
Yes, I am able to do insert into @mv_table
select count(*) from @mv_table
no problem.
The issue is:
If I need to create the sql dynamically, like this:
set @mv_sql = 'select count(*) from @mv_table'.
Then I got the scope issue.
I have 6 table variables.
I need to do some sql execution based on which table contains what kind of variables.
Any hints for how to resolve the issue?
Thanks.
April 3, 2002 at 6:11 pm
Also, in my another piece of code, I need to use
table variable and dynamic sql.
I need to handle a dynamic search, the input search criteria will be any or combination of any of 44 fields.
Originally, I plan to do a dynamic sql and put the result into a table variable. Since I can't do this:
insert into @mv_table
exec (@mv_dynamic_sql)
then, Can I do this:
create table #temptable
then insert the search result to #temptable?
Comparing with using a table variable, is it true that using #temptable will be much slower?
The table will only have one column (t_id).
Normally, the search result will be from
1 to 100,000 records, depending on the search
criteria.
OR, instead of doing dynamic sql, I can do this:
select t_id
from mv_user_table
where t1 = coalesce ( @input_t1, t1 )
and t2 = coalesce (@input_t2, t2)
.....
and t44 = coalesce (@input_t44, t44)
comparing with using dynamic sql, which way is better?
Thanks in advance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply