Sorting issue in EXEC sp_executesql I have follow code and after I ran them in SQL Server Management Studio, I expect to get same results because those 3 parts created exact same SQL Statement. Part 3 SQL statement is created by part 1.
Part 1: run SQL statement using EXEC sp_executesql passing parameters.
Part 2 and 3 just run Select statement 
I got same records, however results show different sorting sequence for column FSONUM, anyone knows why?
Any suggestions/helps would be greatly appreciated.
Thanks

Code:

declare @startDate datetime = cast('2019-07-17' as datetime),

    @endDate datetime =cast('2019-07-17' as datetime),

    @sortQualifier varchar(20)='date',

    @sortOrder varchar(20) ='D',

    @sort varchar(100),

    @SQLString nvarchar(max) ,

    @ParamDefinition nvarchar(500) = N'@startDate datetime, @endDate datetime'

IF @sortOrder &lt;&gt; '' SET @sort = ' DESC'

IF @sortQualifier = '' SET @sort = ''

SET @SQLString = N'SELECT manifestDate, FSONUM, FPIECES,FWEIGHT,FREFERENCE,FINSTRUCT FROM psorderh WHERE manifestDate &gt;= @startDate AND manifestDate &lt;= @endDate ' +

        CASE @sortQualifier

            WHEN 'date' THEN N'ORDER BY manifestDate'

            WHEN 'tracking' THEN N'ORDER BY FSONUM'

            WHEN 'service' THEN N'ORDER BY FSERVICE'

            WHEN 'pieces' THEN N'ORDER BY FPIECES'

            WHEN 'weight' THEN N'ORDER BY FWEIGHT'

            WHEN 'consignee' THEN N'ORDER BY FSCOMPANY'

            WHEN 'city' THEN N'ORDER BY FSCITY'

            WHEN 'state' THEN N'ORDER BY FSPROV'

            WHEN 'zip' THEN N'ORDER BY FSPOSTAL'

            WHEN 'customer' THEN N'ORDER BY FREFERENCE'

            WHEN 'part' THEN N'ORDER BY FINSTRUCT'

        END + @sort

EXEC sp_executesql @SQLString, @ParamDefinition, @startDate, @endDate

SELECT manifestDate, FSONUM, FPIECES, FWEIGHT,FREFERENCE,FINSTRUCT
FROM psorderh WHERE manifestDate &gt;= @StartDate AND manifestDate &lt;= @EndDate ORDER BY

    CASE @sortQualifier

        WHEN 'date' THEN manifestDate

        WHEN 'tracking' THEN FSONUM

        WHEN 'service' THEN FSERVICE

        WHEN 'pieces' THEN FPIECES

        WHEN 'weight' THEN FWEIGHT

        WHEN 'consignee' THEN FSCOMPANY

        WHEN 'city' THEN FSCITY

        WHEN 'state' THEN FSPROV

        WHEN 'zip' THEN FSPOSTAL

        WHEN 'customer' THEN FREFERENCE

        WHEN 'part' THEN FINSTRUCT

        END  DESC

    END

SELECT manifestDate, FSONUM, FPIECES, FWEIGHT,FREFERENCE,FINSTRUCT FROM psorderh WHERE manifestDate &gt;= @StartDate AND manifestDate &lt;= @EndDate ORDER BY manifestDate DESC
You are only ordering by one column. If there is a tie, you haven't told SQL Server how you want those rows with the same value of that column to be sorted, so they can be returned in any order SQL Server likes (why would it sort by FSONUM and not some other column, when you haven't indicated that that's a column whose order you care about?). And yes this can change between executions.

If you order by manifestDate and expect all of the values for FSONUM to be sorted when the date column has the same value, then just hard-code FSONUM as the second order by:

SET @sort = N', FSONUM';


This is okay to do even when FSONUM is also the first choice.

For a lot more on dynamic sorting, see Conditional Order By.