Dynamic Tables.

hello everyone,

Thank you Darrell for your help on my "Firehose" problem.

Well, I have another question for everyone. I have three tables with the same columns: Products, Products_Sold, and Products_StandBy. I have a Stored Procedures that gets the products: Products_Get, Products_Sold_Get, and Products_StandBy_Get. I want to be able to only have one stored procedure that gets from all tables depending on what @.Table variable I give it.

I tried the following, but it didn't work:

CREATE Procedure CMRC_Products_Get
 @.Table
AS
CREATE TABLE #TempTable
(
 ProductID int,
 Chest nvarchar (50),
 ItemSize nvarchar (50)
)
INSERT INTO #TempTable
(
 ProductID,
 Chest,
 ItemSize
)
SELECT
 ProductID,
 @.Table.Chest,
 CMRC_WomensSizes.ItemSize
FROM
 @.Table INNER JOIN
 CMRC_WomensSizes ON @.Table.Chest = CMRC_WomensSizes.Chest

I get an error message on the line: "@.Table.Chest,"

So, my question would now be, how do I go about doing what I'm attempting here? Anybody? Thanks ahead for any help.

Sincerely,

AlecFor what you are trying to do you will need to build a dynamic SQL string and then exec it from within your proc; this will work with out using a temp table; if you need the temp table the syntax may be a bit different - can't remember.

CREATE Procedure CMRC_Products_Get
 @.Table
AS
 Declare @.SQL varchar(1000)
 SET @.SQL = 'SELECT ProductID, ' +
 @.Table +
 '.Chest, ' +
 'CMRC_WomensSizes.ItemSize ' +
 'FROM ' +
 @.Table +
 'INNER JOIN '
 'CMRC_WomensSizes ON ' +
 @.Table +
 '.Chest = CMRC_WomensSizes.Chest'
 EXEC(@.SQL)
GO

This will require SELECT permissions on all tables involved in the query as well as EXEC on the stored procedure. I have had to do this in the past but I believe in your situation you might want to consider a small database design change.

Why not create a new table Status, and have StatusID as a FK in the Product table and forget about the Products_Sold and Products_StandBy tables, then you can pass StatusID instead of the name of a Table (which will not require building dynamic sql) - you did say they all have the exact same columns.