Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Dynamic sql table name variable

Suggested Videos
Part 142 - Dynamic SQL in Stored Procedure
Part 143 - Sql server query plan cache
Part 144 - exec vs sp_executesql in sql server

In this video we will discuss how to pass table name dynamically for stored procedure in sql server. This is one of the sql questions that is very commonly asked. Here is what we want to do.



I have a web page with a textbox as shown below. When I enter a table name in the textbox and when I click "Load Data" button, we want to retrieve data from that respective table and display it on the page.

sql table name variable stored procedure



For the purpose of this demo, we will use the following 2 tables.

how to pass table name dynamically for stored procedure in sql server

Dynamic sql table name variable

SQL Script to create the required tables
Create table Countries
(
     Id int identity primary key,
     CountryName nvarchar(50)
)
Go

Insert into Countries values ('USA')
Insert into Countries values ('India')
Insert into Countries values ('UK')
Insert into Countries values ('Australia')
Insert into Countries values ('Canada')
Go

Create table Employees
(
     ID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Salary int
)
Go

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000)
Go

Create the following stored procedure. Notice we are passing table name as a parameter to the stored procedure. In the body of the stored procedure we are concatenating strings to build our dynamic sql statement. In our previous videos we discussed that this open doors for SQL injection. 

Create procedure spDynamicTableName
@TableName nvarchar(100)
As
Begin
     Declare @sql nvarchar(max)
     Set @sql = 'Select * from ' + @TableName
     Execute sp_executesql @sql
End

So the obvious question that comes to our mind is, why are we not creating parameterised sql statement instead. The answers is we can't. SQL Server does not allow table names and column names to be passed as parameters. Notice in the example below, we are creating a parameterised query with @TabName as a parameter. When we execute the following code, the procedure gets created successfully.

Create procedure spDynamicTableName1
@TableName nvarchar(100)
As
Begin
     Declare @sql nvarchar(max)
     Set @sql = 'Select * from @TabName'
     Execute sp_executesql @sql, N'@TabName nvarchar(100)',
     @TabName = @TableName
End

But when we try to execute it we get an error - Must declare the table variable "@TabName"
Execute spDynamicTableName1 N'Countries'

Add a Web Page to the project that we have been working with in our previous video. Name it "DynamicTableName.aspx". Copy and paste the following HTML on the page.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Employee Search</title>
    <link rel="stylesheet"
        href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"
        type="text/css" />
</head>
<body style="padding-top: 10px">
    <div class="col-xs-8 col-xs-offset-2">
        <form id="form1" runat="server" class="form-horizontal">
            <div class="panel panel-primary">
                <div class="panel-heading">
                    <h3>Table Lookup</h3>
                </div>
                <div class="panel-body">
                    <div class="form-group">
                        <label for="inputTableName" class="control-label col-xs-4">
                            Table Name
                        </label>
                        <div class="col-xs-8">
                            <input type="text" runat="server" class="form-control"
                                id="inputTableName" placeholder="Please enter table name" />
                        </div>
                    </div>
                    <div class="form-group">
                        <div class="col-xs-10 col-xs-offset-2">
                            <asp:Button ID="btnLoadData" runat="server" Text="Load Data"
                                CssClass="btn btn-primary" OnClick="btnLoadData_Click" />
                            <asp:Label ID="lblError" runat="server" CssClass="text-danger">
                            </asp:Label>
                        </div>
                    </div>
                </div>
            </div>

            <div class="panel panel-primary">
                <div class="panel-heading">
                    <h3>Table Data</h3>
                </div>
                <div class="panel-body">
                    <div class="col-xs-10">
                        <asp:GridView CssClass="table table-bordered"
                            ID="gvTableData" runat="server">
                        </asp:GridView>
                    </div>
                </div>
            </div>
        </form>
    </div>
</body>
</html>

Copy and paste the following code in the code-behind page. 

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DynamicSQLDemo
{
    public partial class DynamicTableName : System.Web.UI.Page
    {
        protected void btnLoadData_Click(object sender, EventArgs e)
        {
            try
            {
                if (inputTableName.Value.Trim() != "")
                {
                    string strConnection = ConfigurationManager
                   .ConnectionStrings["connectionStr"].ConnectionString;

                    using (SqlConnection con = new SqlConnection(strConnection))
                    {
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = con;
                        cmd.CommandText = "spDynamicTableName";
                        cmd.CommandType = CommandType.StoredProcedure;

                        SqlParameter param = new
                            SqlParameter("@TableName", inputTableName.Value);
                        param.SqlDbType = SqlDbType.NVarChar;
                        param.Size = 100;
                        cmd.Parameters.Add(param);

                        con.Open();
                        SqlDataReader rdr = cmd.ExecuteReader();
                        gvTableData.DataSource = rdr;
                        gvTableData.DataBind();
                    }
                }
                lblError.Text = "";
            }
            catch (Exception ex)
            {
                lblError.Text = ex.Message;
            }
        }
    }
}

At this point, run the application and type the following text in the "Table Name" textbox and click "Load Data" button. Notice "SalesDB" database is dropped. Our application is prone to SQL injection as we have implemented dynamic sql in our stored procedure by concatenating strings instead of using parameters.
Employees; Drop database SalesDB

One way to prevent SQL injection in this case is by using SQL Server built-in function - QUOTENAME(). We will discuss QUOTENAME() function in detail in our next video. For now understand that by default, this function wraps that string that is passed to it in a pair of brackets.
SELECT QUOTENAME('Employees') returns [Employees]

Modify the stored procedure to use QUOTENAME() function as shown below.

Alter procedure spDynamicTableName
@TableName nvarchar(100)
As
Begin
     Declare @sql nvarchar(max)
     Set @sql = 'Select * from ' + QUOTENAME(@TableName)
     Execute sp_executesql @sql
End

At this point, type the following text in the "Table Name" textbox and click "Load Data" button. Notice you will see a message - Invalid object name 'Employees; Drop database SalesDB'. Also "SalesDB" database is not dropped. 
Employees; Drop database SalesDB

The entire text in "Table Name" textbox is wrapped in a pair of brackets by the QUOTENAME function and is treated as table name. Since we do have a table with the specified name, we get the error - Invalid object name.

4 comments:

  1. Dear Venkat sir. one thing as i think your video is already very much easy and clear explanation. so no need of this blog every thing we got from tutorial. you have do lot of extra works for us we are thankful very much for your dedication and determination of help.

    ReplyDelete
  2. can you please make a video on volatile keyword in C#

    ReplyDelete
  3. Must declare the scalar variable "@TableName".
    i have face this error in sql.
    can you help me solve this error

    ReplyDelete
    Replies
    1. Please use
      Create procedure spDynamicTableName
      @TableName nvarchar(100)
      As
      Begin
      Declare @sql nvarchar(max)
      Set @sql = 'Select * from ' + @TableName
      Execute sp_executesql @sql
      End

      Delete

It would be great if you can help share these free resources