How to use an INTEGER value to perform a lookup into a STRING array in a SQL SELECT statement and re-CodePudding

I have a SQL SELECT statement that returns an INTEFER in one of the columns of the result set. Instead of returning the INTEGER, I want to return a STRING from a constant ARRAY using the INTEGER in that column as the index into the ARRAY. For example, I have this SELECT statement that returns an INTEGER:

SELECT iStatus FROM statusTable

Instead of returning the INTEGER iStatus, I want to use the value within the SELECT statement to retrieve a STRING value from an ARRAY.

DECLARE @list varchar (23) = 'APPLE, ORANGE, PEAR, OTHER'

How would I modify the SELECT statement to return the STRING from @List indexed by iStatus?

I have tried the CASE statement, and it works, but I have multiple STRING lookups I have to perform within the same SELECT statement and I would like to find a more elegant solution.

I have tried this, and it does work, but I would like to reduce the number of lines in my SQL statement:

SELECT StringStatus =
   CASE   
      WHEN iStatus = 0 THEN 'Requested' 
      WHEN iStatus = 1 THEN 'Pending'   
      WHEN iStatus = 2 THEN 'Ordered'   
      WHEN iStatus = 3 THEN 'Assigned'   
   END

CodePudding user response：

It goes without saying: storing this stuff in a table is the way to go. What if a new status is added? Do we do a repo-wide find/replace?

That said, a more elegant way to write you CASE statement would be:

CASE @iStatus
   WHEN 0 THEN 'Requested' 
   WHEN 1 THEN 'Pending'   
   WHEN 2 THEN 'Ordered'   
   WHEN 3 THEN 'Assigned'   
END

Then you have CHOOSE which is as close to an array as it gets in T-SQL.

DECLARE @iStatus INT = 0

SELECT StringStatus = CHOOSE(@iStatus 1,'Requested','Pending','Ordered','Assigned')

An even more elegant solution would be a scalar UDF provided that it's inlineable.

CREATE OR ALTER FUNCTION dbo.StringStatus(@iStatus INT)
RETURNS VARCHAR(12) WITH EXECUTE AS CALLER, SCHEMABINDING AS
BEGIN
  RETURN(
   CASE @iStatus
      WHEN 0 THEN 'Requested' 
      WHEN 1 THEN 'Pending'   
      WHEN 2 THEN 'Ordered'   
      WHEN 3 THEN 'Assigned'   
   END);
END;

I say "inlineable" because otherwise the performance will be dreadful. A slightly less elegant (but faster) solution is to create an inline table valued function.

CREATE OR ALTER FUNCTION dbo.StringStatus_itvf(@iStatus INT)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT StringStatus =
   CASE @iStatus
      WHEN 0 THEN 'Requested' 
      WHEN 1 THEN 'Pending'   
      WHEN 2 THEN 'Ordered'   
      WHEN 3 THEN 'Assigned'   
   END;

Storing this stuff in a table is the best option, otherwise the itvf solution is the way I would go. Here is each function in action:

-- Sample Data
DECLARE @things TABLE(N INT)
INSERT @things VALUES(1),(0),(2),(1),(3);

-- Scalar
SELECT t.N, dbo.StringStatus(t.N)
FROM   @things AS t;

-- iTVF
SELECT      t.N, dbo.StringStatus(t.N)
FROM        @things AS t
CROSS APPLY dbo.StringStatus_itvf(t.N);