How to pass table name as parameter to Sql server stored procedure or query

Introduction: In this article I am going to explain how to dynamically pass table name as a parameter/variable to sql server query or stored procedure to get the data of passed table name.
How to pass table name as parameter to Sql server stored procedure or query

Description: While working on project I got the requirement to pass different table name as parameter to stored procedure and get the results of the table being passed as parameter.  If we write SELECT * from @TableName  in stored procedure then it will not work. We have to create dynamic sql query as demonstrated in this article.

Implementation: Let’s understand the concept with a suitable example.
  • First of all create a table and add dummy data into it using the following sql script:

GO
CREATE TABLE tbBookDetails
(
                BookId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                BookName         VARCHAR(100),
                Author                 VARCHAR(100),
                Publisher            VARCHAR(100),
                BookpPrice       DECIMAL(10,2)
)
GO
INSERT INTO tbBookDetails VALUES
('Asp.Net','Ajay','Rozy Publication',1200),
('C#.Net','Bhavuk','Jai Publication',1000),
('VB.Net','Nancy','Rozy Publication',970),
('MVC','Sahil','Amar Publication',1480),
('JAVA','Supreet','Sam Publication',850),
('PHP','Parvesh','Maya Publication',800) 
  • Now let’s write a dynamic sql query and execute it

DECLARE @Sql AS NVARCHAR(MAX), @TableName NVARCHAR(50)
SET @TableName='tbBookDetails'
SET @Sql='SELECT * FROM '+ @TableName
EXEC SP_EXECUTESQL @SQL   

Result will be as shown in image above.

In above script, set any table name of your database  in @TableName parameter  and it will execute the query on the table name that is passed.
  • Now suppose we need to so the same via a stored procedure, then below is the stored procedure for same.
GO
CREATE PROCEDURE DemoSp
(
      @TableName NVARCHAR(50)
)
AS
BEGIN    
     DECLARE @Sql AS NVARCHAR(MAX)
     SET @Sql='SELECT * FROM '+ @TableName
     EXEC SP_EXECUTESQL @SQL   
END

To execute the stored procedure write:
EXEC DemoSp 'tbBookDetails'

Result will be as shown in image above.

Note: We can also get the same result with one line stored procedure as:

CREATE PROCEDURE TestSp
(  
    @TableName NVARCHAR(50)
)
AS
BEGIN        
     EXEC ('SELECT * FROM ' + @TableName)
END

GO
To execute the stored procedure write:

EXEC TestSp 'tbBookDetails'

Result will be as shown in image above.

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..