title: How to return table output from SP_ExecuteSQL
body: <p>I'm trying to create a Stored Procedure that generates a load of SQL dynamically. I'm trying to prevent string injection by using the <code>sp_executesql</code> to pass all of the parameters into the query. The query seems to execute fine but I have two issues. Firstly when I execute using my <code>Alias</code> name which uses the pattern <code>xx\xxxxx</code> it throws an error saying there is an error near the first two letters of the Alias. I think this is due to the NVARCHAR type but not entirely sure how to handle the <code>\</code> in this. Secondly, and more importantly, the overall procedure returns nothing. It tells me that it has executed fine but I don't get my table of results as expected.</p>
  
  <p>I've omitted the statements to generate the parameters for brevity (These work fine if I generate the whole string and Run <code>EXEC string</code></p>
  
  <p>I'm also wary of using global temp tables as this will be run in a multi-user environment</p>
  
  <p><strong><em>Updated with full code</em></strong></p>
  
  <pre><code>DECLARE @IDType NVARCHAR(255) = NULL
  DECLARE @Customer NVARCHAR(MAX) = NULL
  DECLARE @IdentifiedBy NVARCHAR(255) = NULL
  DECLARE @ImpactArea NVARCHAR(MAX) = NULL
  DECLARE @Gateway NVARCHAR(255) = NULL
  DECLARE @ProbabilityRating NVARCHAR(255) = NULL
  DECLARE @ImpactRating NVARCHAR(255) = NULL
  DECLARE @CostRevenue NVARCHAR(255) = NULL
  DECLARE @Status NVARCHAR(255) = NULL
  DECLARE @Keywords NVARCHAR(MAX) = NULL
  DECLARE @govOwner NVARCHAR(255) = NULL
  
  DECLARE @Alias VARCHAR(50)
  DECLARE @Role NVARCHAR(255)
  DECLARE @SQL NVARCHAR(MAX)
  DECLARE @Where NVARCHAR(MAX) = ''
  DECLARE @SQLOrder NVARCHAR(MAX)
  
  SET @Alias = SUSER_SNAME()
  SET @Role =(SELECT [Role] FROM [FB].[Users] WHERE [Alias] = @Alias)
  
  IF @IDType IS NOT NULL
  BEGIN
  IF @IDType = 'Blank'
      SET @Where += ' AND IDType = NULL OR IDType = '''''
  ELSE IF @IDType != 'All'
      SET @Where += ' AND IDType = @IDType'
  END
  IF @Customer IS NOT NULL
  BEGIN
  IF @Customer = 'Blank'
      SET @Where += ' AND Customer = NULL OR Customer = '''''
  ELSE IF @Customer != 'All'
      SET @Where += ' AND Customer = @Customer'
  END
  IF @IdentifiedBy IS NOT NULL
  BEGIN
  IF @IdentifiedBy = 'Blank'
      SET @Where += ' AND IdentifiedBy = NULL OR IdentifiedBy = '''''
  ELSE IF @IdentifiedBy != 'All'
      SET @Where += ' AND IdentifiedBy = (SELECT FB.Alias(@IdentifiedBy))'
  END
  IF @ImpactArea IS NOT NULL
  BEGIN
  IF @ImpactArea = 'Blank'
      SET @Where += ' AND ImpactArea = NULL OR ImpactArea = '''''
  ELSE IF @ImpactArea != 'All'
      SET @Where += ' AND ImpactArea = @ImpactArea'
  END
  IF @Gateway IS NOT NULL
  BEGIN
  IF @Gateway = 'Blank'
      SET @Where += ' AND Gateway = NULL OR Gateway = '''''
  ELSE IF @Gateway != 'All'
      SET @Where += ' AND Gateway = @Gateway'
  END
  IF @ProbabilityRating IS NOT NULL
  BEGIN
  IF @ProbabilityRating = 'Blank'
      SET @Where += ' AND ProbabilityRating = NULL OR ProbabilityRating = '''''
  ELSE IF @ProbabilityRating != 'All'
      SET @Where += ' AND ProbabilityRating = @ProbabilityRating'
  END
  IF @ImpactRating IS NOT NULL
  BEGIN
  IF @ImpactRating = 'Blank'
      SET @Where += ' AND ImpactRating = NULL OR ImpactRating = '''''
  ELSE IF @ImpactRating != 'All'
      SET @Where += ' AND ImpactRating = @ImpactRating'
  END
  IF @CostRevenue IS NOT NULL
  BEGIN
  IF @CostRevenue = 'Blank'
      SET @Where += ' AND CostRevenue = NULL OR CostRevenue = '''''
  ELSE IF @CostRevenue != 'All'
      SET @Where += ' AND CostRevenue = @CostRevenue'
  END
  IF @Status IS NOT NULL
  BEGIN
  IF @Status = 'Blank'
      SET @Where += ' AND Status = NULL OR Status = '''''
  ELSE IF @Status != 'All'
      SET @Where += ' AND Status = @Status'
  END
  IF @Keywords IS NOT NULL
  
  IF @govOwner IS NOT NULL
  BEGIN
  IF @govOwner = 'Blank'
      SET @Where += ' AND govOwner = NULL OR govOwner = '''''
  ELSE IF @govOwner != 'All'
      SET @Where += ' AND govOwner = (SELECT FB.Alias(@govOwner))'
  END
  CREATE TABLE #tmp (
      ID int
      ,FeedbackType varchar(255)
      ,ImpactArea varchar(255)
      ,CreatedDate varchar(11)
      ,Customer varchar(255)
      ,IdentifiedBy varchar(255)
      ,CriticalityRating varchar(255)
      ,govOwner varchar(255)
      ,Status varchar(255)
  )
  
  SET @SQL = 'SELECT
              fb.ID
              ,FeedbackType
              ,ImpactArea
              ,CONVERT(VARCHAR(11), CreatedDate, 3) AS CreatedDate
              ,Customer
              ,IdentifiedBy
              ,CriticalityRating
              ,govOwner
              ,Status
          INTO #tmp
          FROM [FB].[Feedback] fb 
          INNER JOIN (SELECT
                          ID
                          ,MAX(Version) AS MaxVer
                      FROM FB.Feedback
                      GROUP BY ID
          ) mv ON fb.ID = mv.ID AND fb.Version = mv.MaxVer'
  
  SET @SQLOrder = ' ORDER BY [ID] DESC'
  
  IF @Where IS NOT NULL
      SET @Where = ' WHERE ' + (SELECT STUFF(@Where,1 , 4, '')) + ' '
  
  IF (@Role != 'Governance Board' AND @Role != 'Admin')
      BEGIN
      IF @Where IS NOT NULL
          SET @Where += ' AND [Author] = @Alias OR [IdentifiedBy] = @Alias'
      ELSE
          SET @Where = ' WHERE [Author] = @Alias OR [IdentifiedBy] = @Alias'
      END
  
  SET @SQL += @Where + @SQLOrder
  
  EXECUTE SP_ExecuteSQL @SQL
      ,@Alias = @Alias
      ,@Customer = @Customer
      ,@IdentifiedBy = @IdentifiedBy
      ,@ImpactArea = @ImpactArea
      ,@Gateway = @Gateway
      ,@ProbabilityRating = @ProbabilityRating
      ,@ImpactRating = @ImpactRating
      ,@CostRevenue = @CostRevenue
      ,@Status = @Status
      ,@Keywords = @Keywords
      ,@govOwner = @govOwner
  
  SELECT * FROM #tmp
  DROP TABLE #tmp
  </code></pre>