0

enter image description here

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 <> '' SET @sort = ' DESC'

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

SET @SQLString = N'SELECT manifestDate, FSONUM, FPIECES,FWEIGHT,FREFERENCE,FINSTRUCT FROM psorderh WHERE manifestDate >= @startDate AND manifestDate <= @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 >= @StartDate AND manifestDate <= @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 >= @StartDate AND manifestDate <= @EndDate ORDER BY manifestDate DESC
3

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.

  • Hi Aaron thanks. Yes, I can sort more columns, the problem is SQL in part 3 is generated in Part 1, I just printed it and copy & paste it. In my mind record set both should return same. Only difference is one run in EXEC, another one runs directly. – Fred Jul 19 at 17:36
  • Why should something undefined have to run in a way you expect every time? If you throw a deck of cards in the air do you always expect the same % to land face up? I bet if you compare the plans, they're different. – Aaron Bertrand Jul 19 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.