Dynamic SQL

Prerequisite – Difference between Static and Dynamic SQL

Dynamic SQL is a programming technique that could be used to write SQL queries during runtime. Dynamic SQL could be used to create general and flexible SQL queries.

Syntax for dynamic SQL is to make it string as below :

'SELECT statement';

To run a dynamic SQL statement, run the stored procedure sp_executesql as shown below :

EXEC sp_executesql N'SELECT statement';

Use prefix N with the sp_executesql to use dynamic SQL as a Unicode string.

Steps to use Dynamic SQL :

  1. Declare two variables, @var1 for holding the name of the table and @var 2 for holding the dynamic SQL :
    @var1 NVARCHAR(MAX), 
    @var2 NVARCHAR(MAX);
  2. Set the value of the @var1 variable to table_name :
    SET @var1 = N'table_name';
  3. Create the dynamic SQL by adding the SELECT statement to the table name parameter :
    SET @var2= N'SELECT * 
    FROM ' + @var1;
  4. Run the sp_executesql stored procedure by using the @var2 parameter :
    EXEC sp_executesql @var2;

Example –

from geek;

Table – Geek

1 Khushi Jaipur
2 Neha Noida
3 Meera Delhi

Using Dynamic SQL :

@tab NVARCHAR(128), 
SET @tab = N'geektable';
SET @st = N'SELECT * 
FROM ' + @tab;
EXEC sp_executesql @st;

Table – Geek

1 Khushi Jaipur
2 Neha Noida
3 Meera Delhi
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.

Article Tags :
Practice Tags :

Be the First to upvote.

Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.