Here's another alternative you could try:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL,

[StatusID] [int] NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT 'George','Washington','1'

UNION ALL SELECT 'John','Adams','2'

UNION ALL SELECT 'Thomas','Jefferson','3'

UNION ALL SELECT 'James','Madison','4'

UNION ALL SELECT 'James','Monroe','5'

UNION ALL SELECT 'Andrew','Jackson','6'

UNION ALL SELECT 'John Quincy','Adams','7'

--Original order

SELECT * FROM #TempTable ORDER BY ID

--Order by this string order

DECLARE @SortOrder VARCHAR(50)

SET @SortOrder = '3,2,4,5,1,6,7'

SELECT

ID

,FirstName

,LastName

,StatusID

FROM

#TempTable AS tt

INNER JOIN

dbo.DelimitedSplit8K(@SortOrder,',') AS dsk

ON tt.StatusID = dsk.Item

For those who don't have the DelimitedSplit8K function already:

CREATE FUNCTION [dbo].[DelimitedSplit8K]

(

@pString VARCHAR(8000)

,@pDelimiter CHAR(1)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

WITH E1(N)

AS (

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

UNION ALL

SELECT

1

),--10E+1 or 10 rows

E2(N)

AS (

SELECT

1

FROM

E1 a

,E1 b

),--10E+2 or 100 rows

E4(N)

AS (

SELECT

1

FROM

E2 a

,E2 b

),--10E+4 or 10,000 rows max

cteTally(N)

AS (

SELECT TOP (ISNULL(DATALENGTH(@pString),0))

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM E4

),

cteStart(N1)

AS (

SELECT

1

UNION ALL

SELECT

t.N + 1

FROM

cteTally t

WHERE

SUBSTRING(@pString,t.N,1) = @pDelimiter

),

cteLen(N1,L1)

AS (

SELECT

s.N1

,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)

FROM

cteStart s

)

SELECT

ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)

,Item = SUBSTRING(@pString,l.N1,l.L1)

FROM

cteLen l ;

GO