Execute SQL Query In String Format

Learn how to execute a SQL query that is created in a string format dynamically.

There are times when we need to pass a SQL query as a string that has been created dynamically and execute it on database or from the code. For this purpose, we can use a built-in stored procedure, sp_executesql.
 
Stored procedure, sp_executesql executes a SQL statement or batch that can be reused many times, or one that has been built dynamically. 
 
Here is the syntax: 
  1. sp_executesql [ @stmt = ] statement  
  2. [  
  3. { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }  
  4. { , [ @param1 = ] 'value1' [ ,...n ] }  
  5. ]  
Here is an example where SQL is compiled as a string, str1. The SELECT SQL statement is executed via the string parameter passed to the sp_executesql.
  1. declare @str1 nvarchar(200) -----declare a variable  
  2. set @str1='SELECT * FROM tablename' --- set your query to variable  
  3. exec sp_executesql @str1  
Here is a free eBook: Basic SQL Queries