Passing table name in sql stored procedure

Question

Is it possible to pass the table name as input parameter to the stored procedure?

For example:

create procedure test
@tablename char(10)
as
begin
select * from @tablename
end
go

I know this does not work. So what is the best way if I want to pass the table name into the stored procedure?

Many thanks

Answer 1

The safest way to do this is via a view.

Create a view which unions all the tables you may wish to access (and which must all have the same column structure), and prefix the rows with the table name.

CREATE VIEW MultiTable
AS
    SELECT 'table1' AS TableName, * FROM table1
    UNION ALL
    SELECT 'table2' AS TableName, * FROM table2
    UNION ALL
    SELECT 'table3' AS TableName, * FROM table3

Your stored procedure can now filter on the table name:

CREATE PROCEDURE test
    @TableName varchar(100)
AS
    SELECT * FROM MultiTable WHERE TableName = @TableName

This is safer than using dynamic SQL creation and execution.