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

Solution 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.

Solution 2

You would need to use dynamic SQL, but you need to be aware of potential sql injection risks you open yourself up to as if @tablename contained something dodgy, you could end up in a world of pain.

e.g.

-- basic check to see if a table with this name exists
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = @tablename)
    RETURN

DECLARE @sql NVARCHAR(100)
SET @sql = 'SELECT * FROM ' + QUOTENAME(@tablename)
EXECUTE(@sql)

You need to be very careful with this approach, make sure you don't open up a can of security worms.

My other concern is that you may be trying to make generic data access sprocs which is usually a bad idea. Obviously I don't know your use case.

Solution 3

DECLARE @Name VARCHAR(50)
SET @Name='Company'

EXEC('SELECT * from ' + @Name )

use this way to get record from database.