Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unanswered: Stored Proc Parameter for Table Name

    Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc

    CREATE PROCEDURE [dbo].[sp_Update]
    @DistributorID int,
    @TableName varchar(50)
    AS
    UPDATE C
    SET C.UnitCost = T.[Price]
    FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code)
    GO

    NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.

    Is it possible to use a parameter as a table name? OR is there another way to do this?

    Thanks in advance for your help!
    JayD
    Boulder, CO, USA

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    I think you might be able to use the @TableName parameter if you build a SQL string and use the sp_executesql proc or the execute() method. But I've never tried this with a table name.
    Last edited by peterlemonjello; 09-29-04 at 14:43.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is that the SELECT statement doesn't allow using strings for the server name, not that the stored procedure parameter can't be used to substitute a value... You can work around this limitation by using dynamic SQL, something like:
    Code:
    EXECUTE ('UPDATE C
    SET C.UnitCost = T.[Price]
    FROM (tbl_Catalog C INNER JOIN ' + @TableName 
    + ' T ON C.Code = T.Code)')
    Keep in mind though that permissions need to be set to allow the user to actually do the update when the UPDATE is executed dynamically.

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does every table in your DB have those columns in the table?

    It's like giving Joe Pissarchek(sp?) a football....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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