Dynamic SQL Query

SQL Server different ways of running a dynamic SQL Query statement:
1.Query with parameters
2.EXEC
3.Sp_Executesql

1. Query with parameters
It is as simple as the following example shows.

DECLARE @emp varchar(75)
SET @emp = 'Anu'
SELECT * FROM employee WHERE EmpName = @emp

2. EXEC

SQL statement is built on the fly

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @ emp varchar(75)
SET @columnList = 'EmpId, Salary, EmpName'
SET @emp = '''Anu'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM employee WHERE EmpName = ' + @emp
EXEC (@sqlCommand)

3. sp_executesql

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @emp varchar(75)
SET @columnList = 'EmpId, Salary, EmpName'
SET @emp = '''Anu'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM employee WHERE EmpName = ' + @emp
EXECUTE sp_executesql @sqlCommand, N'@emp nvarchar(75)', @emp = @emp

So here are three different ways of writing dynamic queries