SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Kuala Lumpur
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pass Input Parameter as Table Name

    How do I pass an input parameter as table name for a stored procedure? Originally, my stored procedure is like that :


    CREATE PROCEDURE GetDescriptions
    (
    @ProductID int
    )
    AS

    SELECT Products.*, Boots.*
    FROM Products INNER JOIN Boots
    ON Products.ProductID = Boots.ProductID
    WHERE Products.ProductID = @ProductID



    I want the input parameter replaces the Boots table, I did something like that :


    CREATE PROCEDURE GetDescriptions
    (
    @ProductID int,
    @TableName varchar(10)
    )
    AS

    DECLARE @strProductID varchar(10)
    DeCLARE @query varchar(100)

    set @strProductID = cast(@ProductID as varchar(10))
    set @query = 'Select Products.*,'" + @TableName + "'.*
    From Products INNER JOIN '" + @TableName + "' on Products.ProductID = '" + @TableName + "'.ProductID
    WHERE Products.ProductID = ' + @strProductID

    exec(@query)


    but it doesn't work ...

    Anyone could help me? Thanks here

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    This need is often an indication of a poor datamodel. E.g. you have multiple tables with the same type of content. In that case it is better to have one table with an added type column. It would better to adress that contingency first.

    The problem with your current code is the apostrophes surrounding the table name.

    Code:
    set @query = 'Select Products.*," + @TableName + ".*
    From Products INNER JOIN " + @TableName + " on Products.ProductID = " + @TableName + ".ProductID
    WHERE Products.ProductID = " + @strProductID

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Kuala Lumpur
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie
    This need is often an indication of a poor datamodel. E.g. you have multiple tables with the same type of content. In that case it is better to have one table with an added type column. It would better to adress that contingency first.

    The problem with your current code is the apostrophes surrounding the table name.

    Code:
    set @query = 'Select Products.*," + @TableName + ".*
    From Products INNER JOIN " + @TableName + " on Products.ProductID = " + @TableName + ".ProductID
    WHERE Products.ProductID = " + @strProductID


    Thanks, I get it !


Bookmarks

Posting Permissions

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