Execute SQL Query In String Format

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) -----declare a variable
set @str1='SELECT * FROM tablename' --- set your query to variable
exec sp_executesql @str1

Here is a free eBook: Basic SQL Queries