title: SQL Server stored procedure based on PIVOT and parameterized query not sorting properly
body: <p>I am using a SQL Server stored procedure to generate a data table I use as a datasource for a WPF datagrid. The data are in two tables of parent [Sample] child [SampleConstituent] relationship and I am using a PIVOT to generate columns for the data records in the child table. The query uses parameters so that I can filter the records returned to the datagrid.</p>
  
  <p>My problem is that I would like to return a TOP N result set from the query based on the most recent records. I have the following stored procedure and everything works except the data are always returned oldest records first. The <code>TOP N</code> filter is thus returning the oldest records and not the most recent. <code>Sample.SampleDateTime</code> is the column in the parent table I wish to sort by.</p>
  
  <p>I have tried so many iterations that my novice brain is in knots!</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spSampleDisplayAllParams] 
  -- Add the parameters for the stored procedure here
  @fromDate DATE = '2013-01-01', 
  @toDate DATE = '2100-01-01',
  @ProductName NVARCHAR(50) = '%',
  @SampleNumber NVARCHAR(50) = '%',
  @numSamples NVARCHAR(50) = 200
  AS
  BEGIN
  SET NOCOUNT ON;
  
  DECLARE @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)
  
  select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ConstituentName) 
                  from SampleConstituent
          FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)') 
      ,1,1,'')
  PRINT @cols
  
  set @query 
    = 'SELECT top (' + @numSamples + ') * from  
       (SELECT TOP 100 PERCENT  s.SampleID, s.SampleNumber, s.SampleDateTime, s.ProductName, sc.ConstituentName, sc.ConstituentValue
  FROM         dbo.Sample s INNER JOIN
                    dbo.SampleConstituent sc ON s.SampleID = sc.SampleID
  WHERE (s.Active = 1) AND 
  (s.ProductName Like  ''' + @ProductName + ''') AND
  (s.SampleNumber Like  ''' + @SampleNumber + ''') AND
  (s.SampleDateTime BETWEEN ''' + CONVERT(nvarchar(24), @FromDate, 121) +''' AND'''+     CONVERT(nvarchar(24), @ToDate, 121) +''')
  ORDER BY s.SampleDateTime        )     x
       pivot 
       (
          max(ConstituentValue)
          for ConstituentName in (' + @cols + ')
       ) p  '
  
  execute(@query)
  END
  </code></pre>