Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure?

For example, suppose you have several views of the same table. They all have the exact same structure.

You want a stored procedure that can be run for any of the views.

Something like:

create procedure myprocedure
@tableName varchar(50) = ''
select blah from @tableName where blah = blah2

When I try to do this, I get

Must declare the table variable @tablename

Any ideas how I can do this?

Object names cannot be parameterized.

You need to use dynamic SQL:

CREATE PROCEDURE MyProc
(
    @schemaName sysname,
    @tableName sysname,
    @blah2 int
)
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @sql nvarchar(MAX)

    SET @sql = N'SELECT blah FROM '
        + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' WHERE blah = @blah2'

    EXEC sp_executesql @sql, N'@blah2 int', @blah2

END

Note that sysname is (currently) defined as nvarchar(128).

StackExchange Database Administrators Q#19564, answer score: 11