0

Is there anyway i can do something like

Is there anyway i can get a variable into a FROM clause. Like this

SELECT * FROM myGetTableFunc(@tableName)

SELECT * FROM EXEC('SELECT * FROM ' + @tableName)

SELECT * INTO #myUndeclaredTable FROM @tableName

I think you get the point.

Basicly i will have a few tables with shared common columns like a sorting key etc. In a stored procedure i use these shared columns to work the table and join some stuff and then return it.

But currently I will need a new stored procedure for each table because I can't use a variable in the FROM clause. Is my only solution to turn the whole thing into dynamic SQL?

Things such as OPENQUERY OPENROWSET will not be possible.

I thought about something like 

EXEC ('SELECT * INTO #myTempTable FROM ' + @tableName)
SELECT * FROM #myTempTable

But #myTempTable don't seem to be visible even though i thought it had scope for the entire session.

Thank you for any help!

CodeMonkey
  • 3,418
  • 4
  • 30
  • 53

1 Answers1

1

Try this:

DECLARE @TableName AS VARCHAR(100)
SELECT @TableName = 'myTable'

EXECUTE ('SELECT * INTO #TEMP  FROM ' + @TableName +'; SELECT * FROM #TEMP;')

This was just taken from here:

TSQL select into Temp table from dynamic sql

And look here:

dynamically creating the #temp table and then used to insert the data

Community
  • 1
  • 1
Ric
  • 12,855
  • 3
  • 30
  • 36
  • Hey i got it working using ##Table, which is very global. How do i solve concurency issues if the SP is running concurrently? – CodeMonkey Jan 15 '13 at 11:23
  • you could use table hints as seen here: http://msdn.microsoft.com/en-gb/library/ms187373.aspx, most notably TABLOCKX. play around a bit, I'm sure you'll find something. – Ric Jan 15 '13 at 11:25
  • Thanks, it seems complex I will throw it in a transaction and set a restrictive isolation level i think. It won't be run that often. – CodeMonkey Jan 15 '13 at 11:35