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:
- sp_executesql [ @stmt = ] statement
- [
- { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
- { , [ @param1 = ] 'value1' [ ,...n ] }
- ]
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.
- declare @str1 nvarchar(200)
- set @str1='SELECT * FROM tablename'
- exec sp_executesql @str1
Here is a free eBook:
Basic SQL Queries