Geek asks and answers

Passing table name in sql stored procedure

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

For instance:

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

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

Many thanks

+5
source share
3 answers

The safest way to do this is through presentation.

Create a view that combines all the tables that you might want to access (and which should have the same column structure), and the row prefix 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 the table name:

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

This is safer than using and creating dynamic SQL.

+10
source

SQL, sql, , , @tablename - , .

.

-- 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)

, , .

, , . , .

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

EXEC('SELECT * from ' + @Name )

use this method to get a record from the database.

+2
source

More articles:


All Articles