0

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
PathJim
  • 3
  • 3
  • 3
    SQL Server doesn't have an array data type. But it does have a type **designed** for storing multiple values, unlike string. That type is a *table*. So if you want a far easier life, apply some database design basics and *normalize* your data. – Damien_The_Unbeliever Dec 14 '22 at 14:05
  • 1
    Does this answer your question? [Using T-SQL, return nth delimited element from a string](https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string) – Thom A Dec 14 '22 at 14:10
  • Assuming I prefer to use the list of VARCHARS, then what would be the best method. This is a one-time query. I don't need to go through the hassle of getting new tables approved in the PROD db. – PathJim Dec 14 '22 at 14:11
  • Your `CASE` can be written more succinctly with `CHOOSE` (internally this will yield the same parse tree; it's strictly syntactic shorthand). Going for an "elegant" solution in T-SQL is a bit like asking a hippo to dance; parsing constant delimited strings because you can't get tables is certainly not one of those. – Jeroen Mostert Dec 14 '22 at 14:22
  • You could declare the string as `DECLARE @list varchar (50) = '["APPLE", "ORANGE", "PEAR", "OTHER"]'` and then use `JSON_VALUE(@list, CONCAT('$[',iStatus,']'))` - I'm not sure what the performance penalty of that vs `CASE` would be though. – Martin Smith Dec 14 '22 at 14:45
  • Or even better create tables to hold your string values. That has the appearance of a standard lookup table to me. If you use a table for your lookup values you simply join to it and get the string value back. – Sean Lange Dec 14 '22 at 16:01
  • [`Case`](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15) is an _expression_, not a _statement_, in T-SQL. A _simple_, rather than _searched_, `case` would be less verbose: `case iStatus when 0 then 'Requested' when 1 then 'Pending' ... else 'Unexpected Value' end`. – HABO Dec 14 '22 at 16:16

1 Answers1

0

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);
Bernie156
  • 81
  • 2