Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Romania
    Posts
    4

    Unanswered: Table name in a stored procedure

    How can be used in a stored procedure the name of a table that was passed as a varchar parameter? The table name must be used in the "from" clause of a "select" statement without calling the "EXECUTE" or "sp_executesql". Is it possible?
    Marius G.

  2. #2
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    yes

    create procedure usp_tableget @tbname varchar(100)
    as
    exec('select * from '+@tbname)

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll assume from your references that you are dealing with Microsoft SQL. The answer is essentially the same no matter which SQL engine you are using, although the implementation details are different.

    There are ways to do it, but they all involve dynamic SQL of some sort. Obviously you can get there via EXECUTE ('SELECT * FROM ' + @t) and similar tricks using sp_sqlexec, but you've ruled those out. You can also get there via OPENQUERY, and via all kinds of twists that do basically the same kind of thing.

    You are asking for dynamic SQL. You may have to live with getting dynamic SQL!

    -PatP

Posting Permissions

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