Can I use a variable in the From Clause of a Select statement

[deharris2003]

I have this query that is in a Stored Preocedure. I want to be able to call this stored procesure with different variables. Problem is that is will not compile with a variable in the From statement. I keep getting a message that the variable must be decared but that is strage because I have already declared the variable. here is the stored procedure

/*
This procedure will be used to update the Pasadena, Klein,
Humble and DeerPark Files on Online_Media

Variables are defined as

@TableName = Original Table Name
@IDXHMOID = the IDX HMO Health Plan Number
@HMOMnemonic = Three letter mnemonic for file being
processed
@FileName = Name of File that that is being processed this
name should exist in the table HMOFileUpdateList
in the HMOFileName Column
*/

SELECT Name,
(Case When len(Member_ID) = 9 Then
Member_ID + 000
ELSE
Left(Member_ID,9) + Right(Member_ID,3)
End) AS Member_ID,
Address1, Birth_Date, Sex, Home_Phone, PCP_Name,
PCP_Start_Date, PCP_Stop_Date,
' + @IDXHMOID + ' AS HPID, Relation_Flag, Group_ID,
' + Right(@HMOMnemonic,3) + ' AS HMO, SubGroup_ID
FROM @TableName

I also tried to Cast the Variable as varchar and it compiled.

Example:
FROM Cast(@TableName) AS varchar

But then when I run the sp I get an error that says
Invalid object name 'Cast'.

Here is that version


Any Help is appreciated

 

[JohnDTampaBay]

You can't use a variable to reference a table or column name. You will have to use dynamic SQL. Something like this:

DECLARE @sql varchar(1000)

SET @sql = 'SELECT Name,
(Case When len(Member_ID) = 9 Then
Member_ID + 000
ELSE
Left(Member_ID,9) + Right(Member_ID,3)
End) AS Member_ID,
Address1, Birth_Date, Sex, Home_Phone, PCP_Name,
PCP_Start_Date, PCP_Stop_Date,
' + CAST(@IDXHMOID AS varchar)+ ' AS HPID, Relation_Flag, Group_ID,
''' + CAST(Right(@HMOMnemonic,3) + ''' AS HMO, SubGroup_ID
FROM ' +@TableName

PRINT @sql
EXEC(@sql)


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[deharris2003]

Thanks for your help that works great!!!