title: Scope issue with stored procedure string parameter being returned from dynamic SQL
body: <p>I have a stored procedure for creating inventory transactions that requires the assembly of a description. Since other inventory stored procedures will also need to assemble their descriptions similarly, I am trying to create a helper stored procedure.</p>
  
  <p>This helper will use standard parameters and construct the description. The trouble I am having is returning the string <code>Description</code> back to the inventory transaction.</p>
  
  <p>A Inventory transaction calls the helper this way:</p>
  
  <pre><code>declare @TransDescription nvarchar(256) 
  declare @TransDescOut nvarchar(256)
  
  EXEC [dbo].[sp_KF_Helpers_CreateInvTransDescription] 
       @TransactionTypeID, @UserName, @OwnerTypeID, @OwnerID, 
       @TransDesc = @TransDescOut OUTPUT
  
  SET @TransDescription = @TransDescOut
  </code></pre>
  
  <p>Then I use <code>@TransDescription</code> as a value for inserting into column data.</p>
  
  <p>The helper code is:</p>
  
  <pre><code>    CREATE PROCEDURE [dbo].[sp_KF_Helpers_CreateInvTransDescription]
      (   @TransactionTypeID int,
          @UserName nvarchar(256),
          @OwnerTypeID int,
          @OwnerID int,
          @TransDesc varchar(256) OUTPUT
      )
      AS
      BEGIN
      SET NOCOUNT ON;
  
  declare @rslt int = 0
  declare @strTyepID varchar(256) = @TransactionTypeID
  declare @strOwnerID varchar(256) = @OwnerID
  declare @intOwnerTypeID int = @OwnerTypeID
  declare @OwnerStr varchar(256) = 'KF_'
  declare @OwnerIDStr varchar(256) = (select Description from KF_OwnerType where ID = @intOwnerTypeID)
  
  select @OwnerStr = @OwnerStr + @OwnerIDStr
  declare @sql1 nvarchar(4000)
  
  Select @sql1 = 'Select Top 1 (a.Description + '' - '' + ' + @OwnerStr + '.Name) TransDesc 
  from KF_InventoryTransactionType a, KF_OwnerType c, ' + @OwnerStr + ' 
  where a.ID = ' + @strTyepID + ' and ' 
  + @OwnerStr + '.ID = ' + @strOwnerID 
  
  exec SP_EXECUTESQL @sql1, N'@TransDesc varchar output ', @TransDesc output
   End
  </code></pre>
  
  <p>As you can see, I am using dynamic SQL to generate the description. The problem is that the help code generates the correct description, but does not pass it back as output.</p>
  
  <p>Anyone know why or where I am losing scope for returning my output description?</p>