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