Dynamic sql table name variable

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

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.

I have a web page with a textbox. 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.

Copy the SQL Script to create the tables from my blog using the link below

Create the following stored procedure. Notice we are passing table name as a parameter to the stored prcoedure. 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'
End

Execute spDynamicTableName1 N'Countries'

Copy and paste the code from my blog in the code-behind page

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)
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.

[johnwindsor]

Very very nice video tutorial. I respect you so much!

[waqasriaz]

You are a champion !!
Very well explained lectures.

[muhammadrehbarsheikh]

thank you venkat sir.. great explanation.. ALLAH bless you !

[jmboniels]

Great handout. Thanks with Easter blessings this day in Christ

[mohamadhelaly]

thanx again and again. you are awesome

[fredericfantin]

How to variabilize dynamically the table name of a 57k SQL statement ? As we can't build a stored procedure with a table name as a variable, and as a nvarchar(max) viariable can't contains more than 8k SQL statement! It seems that there is no solution with SQL server ?

[dongfanzhang]

Dear Vankat, how to pass two parameters, one as column name and other as the content of the column?
I tried to make a sp, does not seem to work as intended? Can you please give some advice? Thanks a lot

the query I want to achieve will be something like " Select * from customers where CustomerID = 'ALFKI'
so CustomerID and ALFKI will be the parameter @field and @search which are users inputs.

Create PROCEDURE
@field nVarchar(50),
@search nVarchar(50)

AS
BEGIN
DECLARE @sql NVARCHAR(MAX);




END;

[yogitachauhan]

Sir, I have 25 to 30 tables, that I wanted to create dynamically using SP, can you guide me on how can I achieve that?

for example I have 4 tables
1 claim
2 claimEditor
3 claimmapc
4cliam encounter etc...
.
.
.
25

Now on executing SP if I pass 1 in parameter
My table should create as
1 claim_1
2 claimEditor_1
3 claimmapc_1
4cliam encounter _1 and so on

if I pass 2 in param
My table should be created as
1 claim_2
2 claimEditor_2
3 claimmapc_2
4cliam encounter_2
etc

[mehmetkuzu]

Sir, how do we get back inserted value Id in a query where i use dynamic table name

[RehanShaikh-yvnw]

Sir can you please tell us how to pass table name dynamically to function?

[jasmin_yasir]

What is the previous video of this video sir
Please send that link

[purusotam]

sir how to upload excel data with dynamic column name in database.Please respond its urgent...

[projjwalmaiti]

what if we inject by
'Countries]; Drop Databse [SalesDb'