Convert Table Name [varchar] to Table Type

Question

Post reply

Convert Table Name [varchar] to Table Type

Deon Crause

SSC Rookie

Points: 29
More actions
August 28, 2006 at 2:19 am

#191966

I've researched this topic quite a bit, and I believe it can't be done, but I hope someone might have a creative idea?
I want to use the table name (passed as an varchar) to be converted to an actual table type, and update/select/delete from this table without using Dynamic SQL.
E.g:
T_Table1 (id INT, Test VARCHAR(10))
T_Table2 (id INT, Test VARCHAR(10))
UPDATE fnctnReturnTable 'Table1'
SET Test = 'Test'
Is this possible?
Any help would be greatly appreciated!

Viewing 7 posts - 1 through 6 (of 6 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

What is an "Actual Table Type"?

N 56°04'39.16"
E 12°55'05.25"

Ward Pond SSC Eights! Points: 999 More actions · Answer 2

Hi Deon..

If I understand your question correctly, this is not possible. In order to do what you're trying to do, you'll need to employ either Dynamic SQL or sp_executesql.

There might be a way to do this in CLR, but I imagine performance would be an issue at that point.

Take care..

- Ward Pond
blogs.technet.com/wardpond

Deon Crause SSC Rookie Points: 29 More actions · Answer 3

Thanks Ward - thought so.....

Peter:

sorry, may not have been the right choice of words.....i tried to explain it through the example - i assume that when you use a table name in a select statement it points to an db object. What I wanted to do be able to select a db object dynamically without using dynamic SQL.... 🙂 (sounds ironic...)

thanks for the input ppl!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Nice to see you in a good mood today Joe.

Joking aside, have to agree with his thinking. You need to know what you are doing and not just go blindly selecting data on the server... unless you're making some sort of reporting tools for super users where they build their own queries??

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I never said you were wrong Joe and I don't expect that to happen anytime soon... but you need to lighten up today. I know you're one of the Gods of SQL, but acting like God on this forum is my job .

Ward Pond SSC Eights! Points: 999 More actions · Answer 6

About the only place I've seen dynamic SQL used to good effect in this type of scenario is for a search function with wildly variant parameters. There's a series of posts on my blog where I attempt, unsuccessfully, to find a more performant solution than dynamic SQL for optional search parameters.

There are rare times when pop divas of marginal talent need to eat calamari in a Buick. I agree with Joe and RGR'us that we should avoid such unpleasantness whenever possible -- as current events teach us, the ability to do a thing doesn't render it a good idea.

Take care..

- Ward Pond
blogs.technet.com/wardpond