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.

In previous articles i explained how to Remove first or last character from string or column in sql server and Convert or split comma separated string into table rows insql server and Insert multiple records in table in single insertstatement and Sql server query to count male, female and totalemployees  and Difference between primary key and foreign key

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.

• Tweet
• Share
• Share
• Share

Related Post