Difference between exec and sp_execsql in SQL -----------&#

Declare @ tablename varchar (50), @ SQL nvarchar (max), @ orderid varchar (50), @ sql2 nvarchar (max );
Set @ tablename = 'Orders ';
Set @ orderid = n' 10251 ';
Set @ SQL = 'select * from' + quotename (@ tablename) + 'where orderid = '+ Cast (@ orderid as varchar (50) + 'order by orderid DESC'
Exec sp_executesql @ SQL
Go

Declare @ tablename varchar (50), @ SQL nvarchar (max), @ orderid int; Set @ tablename = 'Orders ';
Set @ orderid = 10251;
Set @ SQL = 'select * from' + quotename (@ tablename) + 'where orderid = '+ Cast (@ orderid as varchar (10) + 'order by orderid DESC'
Exec (@ SQL );
-- Note: Only one string variable can be contained in Exec brackets, but multiple variables can be concatenated. If we write Exec as follows:

-- Exec ('select top ('+ Cast (@ topcount as varchar (10) +') * from '+ quotename (@ tablename) + 'order by orderid DESC ');
-- The SQL Compiler will report an error and the compilation fails. If we do this:
Exec (@ SQL + @ sql2 + @ sql3)
Go

Declare @ tablename varchar (50), @ SQL nvarchar (max), @ orderid int; Set @ tablename = 'Orders ';
Set @ orderid = 10251;
Set @ SQL = 'select * from' + quotename (@ tablename) + 'where orderid = @ orderid order by orderid DESC'
Exec (@ SQL); -- when the life variable @ orderid must use exec in the statement of concatenation, if you want to access the variable, you must concatenate the variable content to the dynamically constructedCodeString, such as: Set @ SQL = 'select * from' + quotename (@ tablename) + 'where orderid = '+ Cast (@ orderid as varchar (10 )) + 'order by orderid DESC'

-- The content of the concatenation variable also has performance drawbacks. SQL Server creates a new execution plan for each query string, even if the query mode is the same. To demonstrate this, first clear the execution plan in the cache

-- DBCC freeproccache (this is not the content involved in this article. You can view the msdn