Post reply

Convert Table Name [varchar] to Table Type

  • 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!

     

  • August 28, 2006 at 4:05 am

    #657035

    What is an "Actual Table Type"?

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

  • August 28, 2006 at 10:48 am

    #657152

    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

  • August 28, 2006 at 12:01 pm

    #657173

    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!

  • August 29, 2006 at 10:00 am

    #657303

    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??

  • August 29, 2006 at 11:11 am

    #657312

    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 .

  • August 29, 2006 at 12:01 pm

    #657326

    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

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

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