How to display the string Enum values instead of the number value using SQL

Question

I'm trying to display a list of all Deliveries with the status Dispatched. However, its only returning the number value of the status as opposed to the actual string value. I think this is because I have used Enum to store my status values?

I wish to display the word Dispatched instead of the number value that it represents in the Enum. 

I'm developing in ASP.Net MVC and I'm using the query builder in VS2013.

I'm not sure how to approach this, can anyone please suggest an easy to understand solution using SQL.

Let me know if any additional code is required, and thank you in advance!

Here's the Query I want but it doesn't work:

SELECT Delivery.[Status], 
COUNT(Delivery.DeliveryID) AS Dispatched_Status 
FROM Delivery 
WHERE Delivery.[Status] = 'Dispatched'
GROUP BY Delivery.[Status];

Here's the Query that does work but returns a number value. I tried it this way because Enum stores the string value as a number:

SELECT Delivery.[Status], 
COUNT(Delivery.DeliveryID) AS Dispatched_Status 
FROM Delivery 
WHERE Delivery.[Status] = '1'
GROUP BY Delivery.[Status];

P.S I'm aware that status is a reserved word - will be following the correct naming conventions in future.

Delivery Table Definion

Answer 1

It sounds like you just need to add a lookup table in you DB. Something like

CREATE TABLE [dbo].[StatusLookup](
    [StatusID] [int] NOT NULL,
    [StatusName] [varchar](64) NOT NULL,
    [StatusDescription] [varchar](max),
)

INSERT INTO [dbo].[StatusLookup]([StatusID],[StatusName],[StatusDescription]
VALUES(1, 'Dispatched', 'A dispatched record')
...

Note you'll have to manually do this and make sure to populate it with values that match up with your enum.

Then your query would be

SELECT StatusLookup.[StatusName], 
COUNT(Delivery.DeliveryID) AS Dispatched_Status 
FROM Delivery 
JOIN StatusLookup ON Delivery.Status = StatusLookup.StatusID
WHERE StatusLookup.[StatusName] = 'Dispatched'
GROUP BY StatusLookup.[StatusName];