Use of dynamic SQL statement EXEC and sp_exectesql

Use of dynamic SQL statement EXEC and sp_exectesql EXEC : Using exec command you can create Dyanmic SQL statement but you need to pass extra quotes in parameter value. Check below example :

DECLARE @DynamicQuery varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'FirstName,LastName, City'
SET @city = '''Surat''' -- Extra Quotes added in Parameter
SET @DynamicQuery = 'SELECT ' + @columnList + ' FROM Users WHERE City = ' + @city
EXEC (@DynamicQuery )

sp_exectesql : Using this command you can also build the Dynamic SQL query. advantage of using this approach is you didnot bother about extra quote

DECLARE @DynamicQuery varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'FirstName,LastName, City'
SET @city = 'Surat' 
SET @DynamicQuery = 'SELECT '  + @columnList +  '  FROM Users WHERE City= @city '
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Pass Multiple Parameter in sp_executesql Statement

-- Multiple Parameter in sp_executesql 

DECLARE @ParameterDefinition Nvarchar(1000);

SET @ParameterDefinition = N'@FirstName as Nvarchar(25), @LastName as Nvarchar(25)';

EXECUTE sp_executesql @SQLQUERY, @ParameterDefinition,@FirstName= @FirstName,@LastName= @LastName

Reference URL : http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/