Dynamic SQL – Demo of EXEC and sp_executesql

 

This demo s pretty self explained, you just copy and paste it into SQL Management studio:

DECLARE @cmd NVARCHAR(30) = 'SELECT * FROM Person.Address' 
EXEC (@cmd) 
go

-- Using Dynamic SQL With Parameter 
DECLARE @cmd_param VARCHAR = 1 
DECLARE @cmd VARCHAR(100) = N'Select * from employees where employeeid = ' 
  + @cmd_param 

EXEC (@cmd)

-- DDL - Create Table 
DECLARE @cmd VARCHAR(100) 
SET @cmd = 'Create table TBL (col1 int)' 
EXEC (@cmd) 
SELECT * 
FROM   tbl;

-- DROP 
DROP TABLE tbl

--- sp_executesql --- 
DECLARE @cmd NVARCHAR(100) 

SET @cmd = 
'SELECT * FROM Person.Address where AddressID = @1 and PostalCode = @2' 

EXEC Sp_executesql 
  @cmd, 
  N'@1 int, @2 nvarchar(15)', 
  1, 
  '98011' 

go