Pass Input Parameter as Table Name

[alvinz_c]

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

[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.

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

[alvinz_c]

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.

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

Thanks, I get it !