Dynamic SQL is just like it sounds: creating a SQL statement dynamically.

A normal SQL Statement is just the string you are going to run.

SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1;

But should you have a good business reason to do so, maybe you want to dynamically create the queries. (Personally, Dynamic SQL is the last solution I would do.)

DECLARE @SqlString NVARCHAR(1000),
@ColName NVARCHAR(30) = ‘Field1’
@TableName NVARCHAR(30) = ‘Table1’ ;

SET @SqlString = ‘SELECT ‘ + @ColName + ‘ FROM ‘ + @TableName;
SELECT @SqlString;

The result for that query is going to show: “SELECT Field1 FROM Table1”

You have the string you want to run, but how do you run it? There are a couple of ways.

EXECUTE

Execute will let you run a Dynamic SQL Statement.

Two things it won’t do:

• Keep any kind of execution plan for running the query
• Pass values out of it.

CREATE TABLE TMP (TmpId INT IDENTITY(1,1), TmpVal NVARCHAR(10));

INSERT INTO TMP (TmpVal) VALUES
('A'),
('B'),
('C');

DECLARE @SqlString NVARCHAR(1000),
                   @ColName NVARCHAR(30) = 'TmpVal', 
                  @TableName NVARCHAR(30) = 'Tmp';

--MAKE A SELECT STATEMENT
SET @SqlString = 'SELECT ' + @ColName + ' FROM ' + @TableName;
SELECT @SqlString AS SelectExample;  
EXECUTE (@SqlString);

--MAKE A SELECT STATEMENT WITH TOP 1
SET @SqlString = 'SELECT TOP 1 ' + @ColName + ' FROM ' + @TableName;
SELECT @SqlString AS TmpValTop1;
EXECUTE (@SqlString);

--MAKE A SELECT STATEMENT WITH A 
SET @SqlString = 'SELECT MAX(' + @ColName
   + ') MaxTmpVal FROM ' + @TableName;
SELECT @SqlString AS MaxTmpVal;
EXECUTE (@SqlString);

SET @SqlString = 'UPDATE ' + @TableName 
  + ' SET TmpVal = ''D'' WHERE ' + @ColName + '=''C''';
SELECT @SqlString AS UpdateExample;
EXECUTE (@SqlString);

SET @SqlString = 'INSERT INTO ' + @TableName + ' (TmpVal) VALUES (''C'')';
SELECT @SqlString AS InsertExample;
EXECUTE (@SqlString);

SELECT * FROM TMP;

DROP TABLE Tmp;

SP_EXECUTESQL

sp_ExecuteSQL will allow you to pass variable values into and out of the SQL string you create with Dynamic SQL. It may also save an execution plan.

--TEST DATA--
create table dates (DateId int identity(1,1), DateVal datetime);

insert into dates (DateVal) values
('2017-01-05'),
('2017-01-06'),
('2017-01-07'),
('2017-01-08')
--SHOW THE VALUE IT SHOULD BE
select min(DateVal) AS TestMinVal from dates;

DECLARE @MinDateVal DATETIME;

SELECT @MinDateVal = min(DateVal) FROM dates;

--SHOW THE VALUE USING THE VARIABLE THAT IT STORES
SELECT @MinDateVal MinDateValSet;
--SET TO NULL TO PROVE WE'RE NOT CHEATING :P
SET @MinDateVal = NULL;

--NOTHING UP MY SLEEVE, AND....
SELECT @MinDateVal AS SetToNull;

--THE SQLSTRING WILL BE THE ACTUAL SQL THAT IS EXECUTED.
DECLARE @SqlString NVARCHAR(1000), 
        @ColName NVARCHAR(30) = 'DateVal', 
        @TableName NVARCHAR(30) = 'dates';
--CREATING THE SQL STRING
SET @SqlString = 'SELECT @MinDateVal = MIN(' 
    + @ColName 
    + ') FROM ' + @TableName;

--THIS SHOWS WHAT WILL BE RAN
SELECT @SqlString AS SqlString;
--USING THIS, WE CAN SPECIFY THE MINDATEVAL VARIABLE 
--AND HAVE THE VALUE PASSED OUT FROM THE DYNAMIC SQL
EXECUTE sp_executesql @SqlString 
     , N'@MinDateVal DATETIME OUTPUT' 
     , @MinDateVal = @MinDateVal OUTPUT;
SELECT @MinDateVal;