Dynamic SQL enables us to generate and run SQL statements at run time. Dynamic SQL is needed when our SQL statements contains identifier that may change at different compile times.
Simple Example of dynamic SQL:
CREATE PROC sp_dynamicSQL @table_name NVARCHAR(20), @col_name NVARCHAR(20), @col_value NVARCHAR(20) AS BEGIN DECLARE @Query NVARCHAR(max) SET @Query = 'SELECT * FROM ' + @table_name SET @Query = @Query + ' WHERE ' + @col_name + ' = ' + ''''+@col_value+'''' EXEC (@Query) END
In the above sql query, we can see that we can use above query by defining values in
@table_name, @col_name, and @col_value at run time. The query is generated at runtime and executed. This is technique in which we can create whole scripts as string in a variable and execute it. We can create more complex queries using dynamic SQL and concatenation concept. This concept is very powerful when you want to create a script that can be used under several conditions.
Executing stored procedure
DECLARE @table_name NVARCHAR(20) = 'ITCompanyInNepal', @col_name NVARCHAR(20) = 'Headquarter', @col_value NVARCHAR(20) = 'USA' EXEC sp_dynamicSQL @table_name, @col_name, @col_value
Table I have used