Execute the following script in Query Editor to return an OUTPUT parameter from a dynamic SQL query:

USE AdventureWorks;

DECLARE @SQL NVARCHAR(1024), @ParmDefinition NVARCHAR(1024)

DECLARE @FirstLetter CHAR(1), @LastFirstName NVARCHAR(50)

SET @SQL = N'SELECT @LastFirstNameOUT = max(FirstName)

      FROM Person.Contact'+CHAR(13)+

      'WHERE left(LastName,1) = @FirstLetterOfLastName'

SET @ParmDefinition = N'@FirstLetterOfLastName char(1),

      @LastFirstNameOUT nvarchar(50) OUTPUT'

SET @FirstLetter = 'M'

PRINT @SQL+CHAR(13)

PRINT @ParmDefinition

EXECUTE sp_executesql

      @SQL,

      @ParmDefinition,

      @FirstLetterOfLastName = @FirstLetter,

      @LastFirstNameOUT=@LastFirstName OUTPUT

SELECT

[Last First Name] = @LastFirstName,

Legend='of last names starting with',

Letter=@FirstLetter

GO

Results: