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

Does this answer your question? [How should I pass a table name into a stored proc?](https://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc) — Michael Freidgeim, Oct 27 '21 at 17:39

score 11 · Accepted Answer · answered May 24 '12 at 09:20

11

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.

answered May 24 '12 at 09:20

Daniel Renshaw

33,729
8
75
94

1

It should be noted that, by using "*", the example is assuming that all three tables have the same number of columns. I believe that you would get an error otherwise. Even if this were the case, as a best practice you should probably enumerate them in each SELECT. – Buggieboy Feb 28 '14 at 17:29
@Buggieboy maybe getting that error if the columns are changes in only one of the tables is worthwhile........ – Ian Ringrose May 24 '17 at 10:21

score 4 · Answer 2 · answered May 24 '12 at 09:21

4

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.

answered May 24 '12 at 09:21

AdaTheDev

142,592
28
206
200

1

[Yours is case number one!](http://www.sommarskog.se/dynamic_sql.html#Common_cases) – Bridge May 24 '12 at 09:29
Thanks very much. There is almost negligible chance of sql injection because there is no user entry for the table name. Its only used by the application internally - and I want to avoid creating multiple stored prcs with the same logic except different table names – gunnerz May 24 '12 at 09:44
1

@Bridge - yes, I agree fully. The cases where I've truly needed to use a dynamic table name, I personally have generated the SQL in the application code. But it's not often I've found a genuine use case – AdaTheDev May 24 '12 at 09:44
1

@user1414575 - I would strongly recommend reading Erland Sommerskog's article (@Bridge's link). It does sound like a time saving/generic data access approach you are looking for which in my experience, is bad – AdaTheDev May 24 '12 at 09:46

score 2 · Answer 3 · answered May 24 '12 at 09:26

2

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

EXEC('SELECT * from ' + @Name )

use this way to get record from database.

answered May 24 '12 at 09:26

Umair Noor

442
4
17

Passing table name in sql stored procedure

3 Answers3

Linked