Dynamic SQL in SQL Server

Declare @sql nvarchar(1000)

set @sql= 'Select * from Employees'

Execute sp_executesql @sql

Declare @sql nvarchar(1000)

Declare @params nvarchar(1000)

set @sql= 'Select * from Employees' + ' Where FirstName=@FirstName and LastName=@LastName'

set @params = '@FirstName nvarchar(100), @LastName nvarchar(100)'

--Print @sql

Execute sp_executesql @sql, @params , @FirstName='Test', @LastName ='Test'

Prevent sql injection with dynamic sql

Select * from INFORMATION_SCHEMA.TABLES

DECLARE @SqlString NVARCHAR(1000)

DECLARE @Count INT

DECLARE @TblName SYSNAME = (SELECT MIN(Table_Name) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE')

WHILE (@TblName IS NOT NULL)

BEGIN

SELECT @TblSchema=Table_Schema FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME= @TblName

SET @SqlString= 'SELECT @RecCount=COUNT(1) FROM' + @TblSchema + '.' +@TblName

EXEC sp_ExecuteSql @SqlString,N'@RecCount INT OUTPUT'

,@RecCount= @Count OUTPUT;

PRINT 'The Table:""+@TblSchema+'.'+@TblName"" HAS'+CAST(@Count AS VARCHAR(1000))+'Records';

END