In transact-SQL, what is the syntax to sort my results based on a
specific order such as B, D, C, A rather than A, B, C, D (ascending)
or D, C, B, A (descending)?

One first needs to be able to define the "specific order".  It might
be that you have some function/expression that assigns:

  f(B) = 1
  f(D) = 2
  f(C) = 3
  f(A) = 4

If it is feasible to provide such a mapping (e.g. as user-defined
function), Microsoft's version of Transact-SQL usually doesn't require
you to include that value in the SELECT results in order to sort
(order by) based on it, but these are the cases where you must:

[SELECT (MS Transact SQL Reference)]
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

"...if SELECT DISTINCT is specified, or if the SELECT statement
contains a UNION operator, the sort columns must appear in the select
list."

Sybase's version of Transact-SQL is slightly more flexible, though
there can be a "duplication" of values specified as DISTINCT if the
sort columns are not in the items SELECT'd:

[Sorting query results: the order by clause (Sybase T-SQL)]
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/9555

"Most versions of SQL require that order by items appear in the select
list, but Transact-SQL has no such restriction. You could order the
results of the preceding query by title, although that column does not
appear in the select list...

"A select distinct query with order by or group by can return
duplicate values if the order by or group by column is not in the
select list."

regards, mathtalk-ga

You can use a case expression if the sorted field only has a few
distinct values as follows:

select  MyLetter
from    SomeTable
order by
        case when B then 0
             when D then 1
             when C then 2
             when A then 3
        end

 -- or --

Create a table like this (called LetterSortOrder for example) and then
link the tables and sort using the second table.

Letter  SortOrder
======= ==========
B       1
C       2
D       3
A       4
X       5
Y       6
Z       7
L       8
M       9
K       10

..now you can sort your query results like this:

select  st.MyLetter
from    SomeTable st
join    LetterSortOrder lso on (st.MyLetter = lso.Letter)
order by
        lso.SortOrder

This latter option is great if you want your users to be able to
customize their sorting on reports and the like.  Good luck.

The query should actually look a little more like this:

select  MyLetter
from    SomeTable
order by
        case when MyLetter = 'B' then 0
             when MyLetter = 'D' then 1
             when MyLetter = 'C' then 2
             when MyLetter = 'A' then 3
        end