Today I just got went though this, this helps you to execute a string in SQL. This is how you can execute a string in SQL,
Declare @query Varchar(500)
Set @query = ‘Select * From Employees’
Exec (@query)
OR
EXEC (‘USE AdventureWorks; SELECT EmployeeID, Title FROM HumanResources.Employee;’)
This way you can execute a string in SQL.
This is extremely helpful when you need to add the Where clause based on some criteria. You can have your query in a string and based on that criteria you can append the clause in that string and finally execute the string at the end. This works perfectly.
However this does not work if you want to execute the query and get the result in the Dataset in your .Net application. For that you need to create a #Temp table execute the string and get the result in the #Temp table and then fire the select again on your #Temp table.
Note: Don’t forget to get the brackets ( & ) around your query else SQL will throw an error, Unrecognized stored procedure.
