Sk Asraf Sk Asraf - 1 month ago 10
SQL Question

How to give table name dynamically in stored procedure in SQL Server 2012

I am creating two tables that have some rows and columns. And I created a stored procedure which dynamically takes table name and produce the result.

But when I pas the table name dynamically, then it not work.

CREATE PROC spGetAll (@tableName varchar(50))
AS
BEGIN
SELECT
*
FROM @tableName
END


enter image description here

Answer

Just use dynamic SQL. Example:

CREATE PROC spGetAll (
    @tableName SYSNAME)
AS
BEGIN
    DECLARE @sql nvarchar(max) 

    IF EXISTS (SELECT name FROM sys.objects WHERE name = @tableName)
    SET @sql = 'SELECT * FROM test.'+ QUOTENAME(@tableName)
    EXEC(@sql) -- Or EXEC sp_executesql @sql
END
Comments