Dynamic select using a table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic select using a table variable

Author

Topic

slserra
Starting Member

19 Posts

Posted - 2006-06-23 : 13:54:58

Here's an example of what I'm trying to do. tbl_GroupReal is a real table. @tbl_Group is a table variable. I am not able to access the table variable with a dynamic sql statement.

drop table tbl_GroupReal
create table tbl_GroupReal (GroupId INT)

DECLARE @tbl_Group TABLE (GroupId INT)

INSERT tbl_GroupReal values (10)
INSERT tbl_GroupReal values (20)
INSERT tbl_GroupReal values (30)
INSERT tbl_GroupReal values (40)

INSERT @tbl_Group values (1)
INSERT @tbl_Group values (2)
INSERT @tbl_Group values (3)
INSERT @tbl_Group values (4)

select * from tbl_GroupReal
select * from @tbl_Group

exec ('select * from tbl_GroupReal')

-- this select does not work.
exec ('select * from @tbl_Group')

How can I select from the table variable using a dynamically constructed sql statement?

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-23 : 14:01:38

A table variable is a local variable that exists only in the current session. When you use dynamic SQL, you are starting a new session. You can not access the table variable inside this other session. You could try using global temporary tables instead, but they have their downsides too. What you need to do though is figure out if you really need to use dynamic SQL in the first place. There are lots of ways to avoid it. We could help you with this if you provided more details on why dynamic SQL is being used.

Tara Kizer
aka tduggan

slserra
Starting Member

19 Posts

Posted - 2006-06-23 : 14:20:38

Tara,

The situation I'm trying to avoid is a 'select ... where pkid in (select pkid from tbl_group where memberId = @Mid )' where @Mid is the memberid of the current user using the application.

Since the embedded select (sorry, I dont know the official term for it) always returns the same results for a given memberId, I was thinking it would execute faster if I created a table variable containing the results of the embedded select instead of doing the embedded select on each row.

I was thinking this:

declare @tbl_Group (pkid INT)
insert into @tbl_Group
select pkid from tbl_group where memberId = @Mid

'select ... where pkid in (select pkid from @tbl_group))'

would be faster than this:

'select ... where pkid in (select pkid from tbl_group where memberId = @Mid )'

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-23 : 14:24:37

To determine what is faster, you'll need to test it.

I don't see where you need dynamic SQL in the example that you posted. So possibly you don't need it at all.

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 02:39:08

quote:
was thinking this:

declare @tbl_Group (pkid INT)
insert into @tbl_Group
select pkid from tbl_group where memberId = @Mid

'select ... where pkid in (select pkid from @tbl_group))'

would be faster than this:

'select ... where pkid in (select pkid from tbl_group where memberId = @Mid )'

I dont think so. Set the execution plan and see. You are not passing table name as parameter so you dont need to use Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources