Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: dynamic table names in stored procedure...

    Hello all,

    Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name???

    Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...

    Cheers,
    Justin

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    you will have to create a dynamic query something on these lines

    declare @table nvarchar(20)
    set @table = 'Customers'
    declare @sql nvarchar(100)
    set @sql = 'select * from ' + @table
    exec sp_executesql @sql

    i used the northwind database as an example for this and this example works on the customers table....

    so if your column names will not make a difference then you will need to create a dynamic query on these lines and execute it

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL Injection - look it up or better still read Jesse's link.

    If you must do this then I would recommend at a minimum that you only allow acceptable values for @tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @tablename and only execute the final string if there is.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by pootle flump
    SQL Injection - look it up or better still read Jesse's link.

    If you must do this then I would recommend at a minimum that you only allow acceptable values for @tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @tablename and only execute the final string if there is.
    Paranoia ... a man after my own heart.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •