The SQL code which is executed dynamically is called Dynamic Sql. The Dynamic SQL can help programmers to achieve things that would be difficult to achieve otherwise. The dynamic SQL queries are usually important in search pages where the number of input parameters varies depending on the search criteria selected on the search page. However, dynamic SQL could lead to serious security breaches, extra overheads to run and difficulties to maintain.
Microsoft SQL Server provides two ways which allow the user to execute the dynamic SQL queries:
-
EXEC (short form of EXECUTE)
We usually use the EXEC command to execute stored procedures. But it can be used to run dynamic sql statements as well EXEC). The downside of this command is that it doesn’t provide any interface (input/output parameters) so all input parameters should be concatenated to the SQL dynamic query before the query is submitted to the EXEC command.
This system stored procedure is more preferable because it has an interface (input/output parameters). With sp_executesql, you are more likely to reuse execution plans because you can more easily generate a query string that you invoke repeatedly.
Important Aspects
Dynamic SQL requires that the user executing the code have direct permissions to execute it, even if the dynamic SQL code is within a stored procedure. This limitation was relaxed in SQL Server 2005 where you can impersonate the user and allow the code to run under any security context that you like, so that context could apply to all activities.
Dynamic SQL operates in a batch that is separate from the calling batch. This means that the dynamic batch is parsed, resolved, and optimized as a separate unit.
Dynamic SQL statements using EXEC
In some cases you may want to give the table name, as an input parameter, to a stored procedure and get the rows from that table. So here is an example where we are trying to provide the table name as a parameter so we can use the same SQL statements for more than one table:
DECLARE @TableName VARCHAR(50)
SET @TableName = 'Books'
SELECT * FROM + @TableName
If you try to execute the above code, you will get the following error because the query was built dynamically.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '+'.
In such cases, we have to use the EXEC command to execute the dynamic query:
Declare @TableName varchar(50)
set @TableName='Books'
exec('select * from ' + @TableName)
The above code is supposed to return all the rows from the Books table.
Here is another example where you can set the filter conditions (WHERE clause) dynamically:
DECLARE @i AS INT
SET @i = 1
DECLARE @sql AS VARCHAR(52)
SET @sql = 'SELECT * FROM Books WHERE ID = @i;'
EXEC(@sql)
If you try to run the above code, you will get the following error because, as we said earlier, the dynamic SQL runs as a separate unit and the declaration of the “@i” parameter is out of scope.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@i".
If you want to include a parameter in your dynamic SQL statement, you have to concatenate its value to the dynamic SQL string being built:
DECLARE @i AS INT;
SET @i = 2;
DECLARE @sql AS VARCHAR(52);
SET @sql = 'SELECT * FROM books WHERE ID ='
+ CAST(@i AS VARCHAR(10))
EXEC(@sql);
Dynamic SQL statements using “sp_executesql”
The sp_executesql system stored procedure is more flexible than EXEC command. It supports both input and output parameters which allow you to create query strings with arguments that can reuse execution plans more efficiently than EXEC.
Syntax
EXEC sp_executesql @stmt = ,
@params = ,
The @stmt parameter is the input dynamic batch, which can refer to input and output parameters. This section is similar to a stored procedure's body except that @stmt can be constructed dynamically, whereas a stored procedure's body is static.
The @params parameter is similar to the header of a stored procedure, where you define input/output parameters. In fact, the syntax for @params is identical to that of a stored procedure's declaration section. You can even define default values to the parameters just as you can with a stored procedure. We can create dynamically @params.
Example
DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @Title varchar(200)
SET @columnList = 'Id, Title'
SET @Title = 'Java'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM books WHERE Title = @Title'
EXECUTE sp_executesql @sqlCommand, N'@Title nvarchar(200)', @Title = @Title
This query will display the records which title name is ‘Java’ will be displayed