Using a parameter in the FROM clause of a stored procedure 2

[SouthwestNut]

In summary I have 14 views each ending with a 3 digit number. The 3 digit numbers correspond to different business units, each of which must be kept separate. I have had to create a global temporary table (##) using each view, hence I could have up to 14 temp tables. I have a need to drop them when a new user wants to rerun the report using different criteria.

In VBA, I could write a statement such as the following:
DoCmd.RunSQL "DELETE * FROM [" & vstrTable & "];"

Then I could just pass in the parameter and the contents of the table would be erased.

I am wondering about the likelihood of doing the same thing in a SQL Stored Procedure, using a predefined variable in a FROM clause. Can this be done?

If not I will have to write 14 stored procedures to drop temporary tables, not a big deal, but I hate to clutter my objects window with a lot of redundant procedures.

 

[fluteplr]

you can use the exec statement to do what you want.

something like

create procedure dbo.selectstuff @tablename varchar(50)
exec ('select * from '+@tablename)

 

[chiph]

SouthwestNut -

You could do that via an Exec statement like fluteplr says, but you'd lose all performance gains you'd normally get with a stored procedure. But since you're dropping temp tables, you may not care.

Chip H.

 

[fluteplr]

I figured on a drop table how much difference can it make, since there is no 'query plan' to optimize.