Execution of the stored procedure gives different result comparing with the direct execution

Hi everybody,

I'm getting very puzzling results executing stored procedure vs executing subset of the code from that stored procedure directly.

I have the following code to execute SP:

DECLARE @RC INT
DECLARE @inp_region_nbr TINYINT = 1
DECLARE @inp_npi VARCHAR(100) = '1013924372'
DECLARE @XMLVARCHAR NVARCHAR(MAX)
DECLARE @providerid BIGINT


EXECUTE @RC = [dbo].[usp_File_Extract_ProviderMasterFile_VA_SPLIT] 
   @inp_region_nbr
  ,@inp_npi
  ,@XMLVARCHAR OUTPUT
  ,@providerid OUTPUT

SELECT CAST(@XMLVARCHAR AS XML)

Which gives <Item>

  <CodedSpecialty>282N00000X</CodedSpecialty>  <IsPrimaryTaxonomy>1</IsPrimaryTaxonomy></Item>  </Specialties>

                         /* Specialties */
                       
                           SELECT TOP (1) 
                                  s.CodedSpecialty,
                                  s.IsPrimaryTaxonomy
                           FROM
                           (
                              
                               SELECT TOP (1)
                                      LTRIM(RTRIM(servtax.codeofservicedescription)) CodedSpecialty,
                                      CASE
                                          WHEN servtax.primarytaxonomycd = 1 THEN
                                              1
                                          ELSE
                                              0
                                      END IsPrimaryTaxonomy
                               FROM consume_tbl.PROV_PMF_SERVICETAXONOMY_SPLIT servtax -------------Individual & Organizational Specialties details      
                                   INNER JOIN @providerid_list pl
                                       ON pl.providerid = servtax.providerid
                               GROUP BY servtax.codeofservicedescription,
                                        servtax.primarytaxonomycd
                               UNION
                               /* CVS specialties*/
                               SELECT TOP (1)
                                      ISNULL(oto.codedspecialty, '3336C0003X') CodedSpecialty,
                                      '1' IsPrimaryTaxonomy
                               FROM consume_tbl.PROV_PMF_CAREPROVIDER_SPLIT carepro
                                   INNER JOIN @providerid_list pl
                                       ON pl.providerid = carepro.providerid
                                   LEFT OUTER JOIN consume_tbl.PROV_PMF_ORGTAXONOMY_OVERRIDE oto
                                       ON carepro.providerid = oto.providerid
                               WHERE carepro.sourcesystemcd = 3
                               ORDER BY CodedSpecialty,
                                        IsPrimaryTaxonomy DESC
                           ) s
                           ORDER BY s.IsPrimaryTaxonomy DESC,
                                    s.CodedSpecialty
                       --    FOR XML PATH('Item'), TYPE, ROOT('Specialties')

gives me <Specialties> <Item>

<CodedSpecialty>261QE0700X</CodedSpecialty> <IsPrimaryTaxonomy>1</IsPrimaryTaxonomy> </Item> </Specialties>

How can we explain this flip flopping of the specialty? This is extremely puzzling and I cannot figure out the reason for this. The order by should select the 261QE0700X as specialty always but it doesn't when executed in the procedure.

Can you help me to understand this behavior?