{"id":1741,"date":"2019-04-15T20:23:40","date_gmt":"2019-04-15T13:23:40","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1741"},"modified":"2020-04-11T20:12:27","modified_gmt":"2020-04-11T13:12:27","slug":"sql-server-dynamic-sql","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-dynamic-sql\/","title":{"rendered":"SQL Server Dynamic SQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server dynamic SQL to construct general purpose and flexible SQL statements.<\/p>\n\n\n\n

Introduction to Dynamic SQL #<\/a><\/h2>\n\n\n\n

Dynamic SQL is a programming technique that allows you to construct SQL statements dynamically at runtime. It allows you to create more general purpose and flexible SQL statement because the full text of the SQL statements may be unknown at compilation. For example, you can use the dynamic SQL to create a stored procedure<\/a> that queries data against a table whose name is not known until runtime.<\/p>\n\n\n\n

Creating a dynamic SQL is simple, you just need to make it a string as follows:<\/p>\n\n\n

'SELECT<\/span> * FROM<\/span> production.products';\n<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To execute a dynamic SQL statement, you call the stored procedure sp_executesql<\/code> as shown in the following statement:<\/p>\n\n\n
EXEC sp_executesql N'SELECT<\/span> * FROM<\/span> production.products';<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because the sp_executesql<\/code> accepts the dynamic SQL as a Unicode string, you need to prefix it with an N<\/code>.<\/p>\n\n\n\n
Though this dynamic SQL is not very useful, it illustrates a dynamic SQL very well.<\/p>\n\n\n\n
Using dynamic SQL to query from any table example #<\/a><\/h3>\n\n\n\n
First, declare two variables, @table<\/code> for holding the name of the table from which you want to query and @sql<\/code> for holding the dynamic SQL.<\/p>\n\n\n
DECLARE<\/span> \n    @table<\/span> NVARCHAR<\/span>(128<\/span>),\n    @sql<\/span> NVARCHAR<\/span>(MAX<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, set the value of the @table<\/code> variable to production.products<\/code>.<\/p>\n\n\n
SET<\/span> @table<\/span> = N'production.products'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, construct the dynamic SQL by concatenating the SELECT<\/code> statement with the table name parameter:<\/p>\n\n\n
SET<\/span> @sql<\/span> = N'SELECT * FROM '<\/span> + @table<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, call the sp_executesql<\/code> stored procedure by passing the @sql<\/code> parameter.<\/p>\n\n\n
EXEC sp_executesql @sql;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Putting it all together:<\/p>\n\n\n
DECLARE<\/span> \n    @table<\/span> NVARCHAR<\/span>(128<\/span>),\n    @sql<\/span> NVARCHAR<\/span>(MAX<\/span>);\n\nSET<\/span> @table<\/span> = N'production.products'<\/span>;\n\nSET<\/span> @sql<\/span> = N'SELECT * FROM '<\/span> + @table<\/span>;\n\nEXEC sp_executesql @sql;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The code block above produces the exact result set as the following statement:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> production.products;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To query data from another table, you change the value of the @table<\/code> variable. However, it’s more practical if we wrap the above T-SQL block in a stored procedure.<\/p>\n\n\n\n
SQL Server dynamic SQL and stored procedures #<\/a><\/h3>\n\n\n\n
This stored procedure accepts any table and returns the result set from a specified table by using the dynamic SQL:<\/p>\n\n\n
CREATE<\/span> PROC usp_query (\n    @table<\/span> NVARCHAR<\/span>(128<\/span>)\n)\nAS<\/span>\nBEGIN<\/span>\n\n    DECLARE<\/span> @sql<\/span> NVARCHAR<\/span>(MAX<\/span>);\n    -- construct SQL<\/span>\n    SET<\/span> @sql<\/span> = N'SELECT * FROM '<\/span> + @table<\/span>;\n    -- execute the SQL<\/span>\n    EXEC sp_executesql @sql;\n    \nEND<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement calls the usp_query<\/code> stored procedure to return all rows from the production.brands<\/code> table:<\/p>\n\n\n
EXEC usp_query 'production.brands';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This stored procedure returns the top 10 rows from a table by the values of a specified column:<\/p>\n\n\n
CREATE<\/span> OR<\/span> ALTER<\/span> PROC usp_query_topn(\n    @table<\/span> NVARCHAR<\/span>(128<\/span>),\n    @topN INT<\/span>,\n    @byColumn NVARCHAR<\/span>(128<\/span>)\n)\nAS<\/span>\nBEGIN<\/span>\n    DECLARE<\/span> \n        @sql<\/span> NVARCHAR<\/span>(MAX<\/span>),\n        @topNStr NVARCHAR<\/span>(MAX<\/span>);\n\n    SET<\/span> @topNStr  = CAST<\/span>(@topN as<\/span> nvarchar<\/span>(max<\/span>));\n\n    -- construct SQL<\/span>\n    SET<\/span> @sql<\/span> = N'SELECT TOP '<\/span> +  @topNStr  + \n                ' * FROM '<\/span> + @table<\/span> + \n                    ' ORDER BY '<\/span> + @byColumn + ' DESC'<\/span>;\n    -- execute the SQL<\/span>\n    EXEC sp_executesql @sql;\n    \nEND<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can get the top 10 most expensive products from the production.products<\/code> table:<\/p>\n\n\n
EXEC usp_query_topn \n        'production.products',\n        10, \n        'list_price';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement returns the top 10 products with the highest quantity in stock:<\/p>\n\n\n
EXEC usp_query_topn \n        'production.tocks',\n        10, \n        'quantity';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server Dynamic SQL and SQL Injection #<\/a><\/h2>\n\n\n\n
Let’s create a new table<\/a> named sales.tests<\/code> for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.tests(id<\/span> INT<\/span>); \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement returns all rows from the production.brands<\/code> table:<\/p>\n\n\n
EXEC usp_query 'production.brands';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
But it does not prevent users from passing the table name as follows:<\/p>\n\n\n
EXEC usp_query 'production.brands;DROP<\/span> TABLE<\/span> sales.tests';\n<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This technique is called SQL injection. Once the statement is executed, the sales.tests<\/code> table is dropped, because the stored procedure usp_query<\/code> executes both statements:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> production.brands;DROP<\/span> TABLE<\/span> sales.tests\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> OR<\/span> ALTER<\/span> PROC usp_query\n(\n    @schema<\/span> NVARCHAR<\/span>(128<\/span>), \n    @table<\/span>  NVARCHAR<\/span>(128<\/span>)\n)\nAS<\/span>\n    BEGIN<\/span>\n        DECLARE<\/span> \n            @sql<\/span> NVARCHAR<\/span>(MAX<\/span>);\n        -- construct SQL<\/span>\n        SET<\/span> @sql<\/span> = N'SELECT * FROM '<\/span> \n            + QUOTENAME<\/span>(@schema<\/span>) \n            + '.'<\/span> \n            + QUOTENAME<\/span>(@table<\/span>);\n        -- execute the SQL<\/span>\n        EXEC sp_executesql @sql;\n    END<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, if you pass the schema and table name to the stored procedure, it will work:<\/p>\n\n\n
EXEC usp_query 'production','brands';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, if you try to inject another statement such as:<\/p>\n\n\n
EXEC usp_query \n        'production',\n        'brands;DROP<\/span> TABLE<\/span> sales.tests';\n<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It will issue the following error:<\/p>\n\n\n
Invalid object name 'production.brands;DROP<\/span> TABLE<\/span> sales.tests'.\n<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
More on sp_executesql stored procedure #<\/a><\/h2>\n\n\n\n
The sp_executesql<\/code> has the following syntax:<\/p>\n\n\n
EXEC sp_executesql \n    sql_statement  \n    parameter_definition\n    @param1 = value1,\n    @param2 = value2,\n    ...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
  • sql_statement<\/code> is a Unicode string that contains a T-SQL statement. The sql_statement<\/code> can contain parameters such as SELECT * FROM table_name WHERE id=@id<\/code><\/li>
  • parameter_definition<\/code> is a string that contains the definition of all parameters embedded in the sql_statement<\/code>. Each parameter definition consists of a parameter name and its data type e.g., @id INT<\/code>. The parameter definitions are separated by a comma (,).<\/li>
  • @param1 = value1<\/code>, @param2 = value2<\/code>,… specify a value for every parameter defined in the parameter_definition<\/code> string.<\/li><\/ul>\n\n\n\n
    This example uses the sp_executesql<\/code> stored procedure to find products which have list price greater than 100 and category 1:<\/p>\n\n\n
    EXEC sp_executesql\nN'SELECT<\/span> *\n    FROM<\/span> \n        production.products \n    WHERE<\/span> \n        list_price> @listPrice AND<\/span>\n        category_id = @categoryId\n    ORDER<\/span> BY<\/span>\n        list_price DESC<\/span>', \nN'<\/span>@listPrice DECIMAL<\/span>(10<\/span>,2<\/span>),\n@categoryId INT<\/span>'\n,@listPrice = 100\n,@categoryId = 1;\n<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this tutorial, you have learned how to use the SQL Server dynamic SQL to construct general purpose and flexible SQL statements.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
To prevent this SQL injection, you can use the QUOTENAME()<\/a><\/code> function as shown in the following query:<\/p>\n\n\n