Dynamic SQL | How to code , execute and the potential risk of SQL Injection

Dynamic SQL is a SQL Query that can be generated and executed at runtime. 

There are two methods to execute a dynamic sql query -

Using exec -

declare @var nvarchar(100) = '''O%'''

declare @sql nvarchar(500) = 'Select FullName from dbo.Employees 

WHERE FullName like ' + @var

exec(@sql)

Using sp_executesql

declare @var nvarchar(100) = 'O%'

declare @FN varchar(200)

declare @sql nvarchar(500) = 'Select @FN = FullName from dbo.Employees 

WHERE FullName like @var '

execute sp_executesql @sql , N'@var nvarchar(100) , @FN varchar(200) OUTPUT', @var = @var , @FN = @FN OUTPUT

select @FN as FullName;

SQL Injection

declare @var nvarchar(100) = '''O%''DROP TABLE DynamicTest;'

declare @sql nvarchar(500) = 'Select FullName from dbo.Employees 

WHERE FullName like ' + @var

exec(@sql)