Getting Result of Dynamic SQL into a Variable for Sql-Server

When you create a dynamic query and execute it. You want to get a result or value in variable then it is not possible when you use EXEC (@SQL).

You can achieve it by using EXEC SP_EXECSQL,

DECLARE @SQLCOMMAND NVARCHAR(1000)
DECLARE @NAME VARCHAR(75)
DECLARE @COUNTS INT
SET @NAME = 'TEST'
SET @SQLCOMMAND = 'SELECT @CNT=COUNT(*) FROM MSTEMPLOYEE WHERE FIRST_NAME = @NAME'
EXECUTE SP_EXECUTESQL @SQLCOMMAND, N'@NAME NVARCHAR(75),@CNT INT OUTPUT', @NAME = @NAME, @CNT=@COUNTS OUTPUT
SELECT @COUNTS AS COUNTS