Execute and SP_executeSQL statement in SQL server

Here is posting some real examples hope it would clear all confusion.

Execute works with stored procedure and dynamic query. But sp_executesql works with parameterized dynamic query.
sp_executesql works over execute keyword.
sp_executesql dynamic query must have nvarchar, ntext or  nchar.



declare @query varchar(200) ='select * from sys.procedures';
--EXECUTE @query --Could not find stored procedure 'select * from sys.procedures'.
--EXEC(@query)--working
--EXECUTE(@query)--working
--sp_executesql @query -- Incorrect syntax near 'sp_executesql'.
--execute sp_executesql @query-- Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.





declare @name varchar(20) = 'user';
declare @query varchar(200) ='select * from sys.procedures where name like(''%'+@name+'%'')';
EXECUTE(@query)--working





declare @name varchar(20) = 'usp_Updateusers';
declare @query nvarchar(200) ='select * from sys.procedures where name = @namechars ';--query must be ntext/nchar/nvarchar
execute sp_executeSQL @query, N'@namechars varchar(20)', @name --working





declare @name varchar(20) = 'user';
declare @query nvarchar(200) = N'select * from sys.procedures where name like (''%@namechars%'')';--empty as rezex not handling correctly
execute sp_executeSQL @query, N'@namechars varchar(20)', @name --not working





declare @name varchar(20) = 'user';
declare @query nvarchar(200) = N'select * from sys.procedures where name like (''%'' + @namechars+ ''%'')';
execute sp_executeSQL @query, N'@namechars varchar(20)', @name -- working





--multiple sp_executesql statements
declare @name varchar(20) = 'user';
declare @start varchar(20) = 'usp_get';
declare @query nvarchar(200) = N'select * from sys.procedures where name like ( @startchars + ''%'' + @namechars + ''%'')';
execute sp_executeSQL @query, N'@namechars varchar(20), @startchars varchar(7)', @name, @start -- working