Table Name as Variable ???

Question

Post reply

Table Name as Variable ???

newbie_new

SSC Enthusiast

Points: 162
More actions
March 26, 2009 at 3:27 pm

#219469

Hi:
I have a different situation.
I'm trying to make @lTableName a variable, but of course SQL2005 is telling me i need to make it a Table Variable, which is not one of my options. Any way to get it to work as a variable?
insert @Tmp SELECT SignalID from ReportLogConfig WHERE OPCPath is not NULL and Location = @Location
EXCEPT SELECT SignalID from @lTableName
:unsure:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

What you are trying to do is to compose dynamic sql (because objects change).

Must read all time reference: http://www.sommarskog.se/dynamic_sql.html

Any query needs a fixed object name to act on.

If you still want to compose runtime sql, you would need something like this:

declare @sqlstmt varchar(1000)

Select @sqlstmt = 'select mycol from ' + @tablenamevar ' order by mycol'

exec (@sqlstmt)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

newbie_new SSC Enthusiast Points: 162 More actions · Answer 2

Yeah, i'm totally with you on that, but the problem is this is a stored proc & if i do what you suggest, i receive an error because i'm doing an insert exec when i actually call the stored proc.

i.e Stored Proc say, "insert @Tmp exec(@sql)"

when i call the stored proc it says, insert #temp exec spStoredProc

So i get the error can't Do Nested INSERT EXEC.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

Indeed.

You need another kind of temp table (#mytable) in stead of a table variable (@mytable) to

be able to use it in combination with exec !

--edited--

Regarding the nested exec...

That would mean you call the sproc as part of an insert statement... hence it violates this rule.