Stored procedure with a parameter in FROM clause

Question

Is it possible to create a stored procedure that uses a parameter in the FROM clause?

For example:

CREATE PROCEDURE [dbo].[GetMaxId]
@id varchar(50)
@table varchar(50)
AS
BEGIN
SELECT MAX(@id)
FROM @table
END

Possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) — Tab Alleman, Sep 22 '16 at 12:22
It is SQL Server. i think someone modified the title to make it shorter. — Skylake, Sep 22 '16 at 12:32

score 0 · Answer 1 · answered Sep 22 '16 at 12:24

You cannot pass identifiers as parameters into a query (neither table names nor column names). The solution is to use dynamic SQL. Your syntax suggests SQL Server, so this would look like:

CREATE PROCEDURE [dbo].[GetMaxId] (
    @id varchar(50)
    @table varchar(50)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'SELECT MAX(@id) FROM @table';
    SET @sql = REPLACE(REPLACE(@sql, '@id', QUOTENAME(@id)), '@table', QUOTENAME(@table));

    EXEC sp_executesql @sql;
END;  -- GetMaxId

Stored procedure with a parameter in FROM clause

1 Answers1