title: SQL Server : stored procedure to return a set with 3 Items
body: <p>I need to query some tables in a database and return a set of tables.</p>
  <p>I don't want to create a <code>#TempTable</code>.</p>
  <p>There is a way of doing that without a very complicated procedure?</p>
  <p>So, I guess, the return of the Stored Procedure, should be a set with 3 tables:</p>
  <ol>
  <li>The data queried</li>
  <li>A boolean informing of any errors (I think it can be always false)</li>
  <li>The page count TOTAL</li>
  </ol>
  <pre><code>DECLARE @SQL NVARCHAR(MAX);
  DECLARE @SellerCode int = 569;
  DECLARE @Year int = 2020;
  DECLARE @Name VARCHAR(100) = 'silva';
  DECLARE @Document VARCHAR(100) = null;
  DECLARE @GroupCode INT = NULL;
  DECLARE @UserId int = null;
  
  DECLARE @Paginate BIT = 1;
  DECLARE @PageSize INT = 10;
  DECLARE @PageNumber INT = 1;
  
   SET @SQL= '  
      Select 
      Client.Id,
      Client.Document,
      Client.Name,
      Client.Active,
      Client.Email,
      Client.GroupCode,
      Client.UserId,
  
      Estimate.Paid,
      Estimate.Value,
      Estimate.Year
  
      from Estimate
      INNER JOIN Client
      ON Estimate.ClientId = Client.ClientId
      INNER JOIN Seller
      ON Client.SellerCode = Seller.SellerCode
      Where Client.SellerCode = ' + Convert(varchar(20),@SellerCode);
  
  If (Not @Nome Is NULL)  
   Begin  
   SET @SQL = @SQL + ' AND Client.Name LIKE ''%' + @Name + '%'''  
   End
  
  If (Not @Document Is NULL)  
   Begin  
   SET @SQL = @SQL + ' AND Estimate.Document = ' + Convert(varchar(100),@Document) 
   End
  
   If (Not @GroupCode Is NULL)  
   Begin  
   SET @SQL = @SQL + ' AND Client.GroupCode = ' + Convert(varchar(20),@GroupCode)   
   End
  
   If (Not @Year Is NULL)  
   Begin  
   SET @SQL = @SQL + ' AND Estimate.Year = ' + Convert(varchar(10),@Year)
   End
  
   If (Not @UserId Is NULL)  
   Begin  
   SET @SQL = @SQL + ' AND Client.UserId = ' + Convert(varchar(20),@UserId)   
   End  
  
  EXEC sys.sp_executesql @SQL
  </code></pre>
  <p>I have this query so far. The filters are working, but the returning is only one table.</p>
  <p>Can anyone give me an example of how to do that?</p>
  <p>Thank you!</p>
  <p><strong>Edit:</strong> I need to paginate the result as well. This is why I need the Total count of the First query, so the front can deal with it.
  I inherited a generic Stored Procedure, which is very complex and needs to be simplified to use elsewhere.</p>