How can I avoid "stringly typed" code in T-SQL?-CodePudding

Consider some code like

SELECT
  CASE
    WHEN [DECISION-MAKER] = 'COKE' THEN 'GIVE COKE'
    WHEN [DECISION-MAKER] IN ('PEPSI', 'BLOOD') THEN 'GIVE DEATH'
  END AS [EMPLOYEE-ASSIGNMENT],
  CASE [DECISION-MAKER]
    WHEN 'COKE' THEN 'Employee prefers coke. Give coke.'
    WHEN 'PEPSI' THEN 'Employee prefers pepsi. Give death.'
    WHEN 'BLOOD' THEN 'Employee is some sort of vampire. Give death.'
   END AS [ASSIGNMENT-REASON]
FROM
  (
    SELECT *,
    CASE
      WHEN [COMPLEX-LOGIC-1] THEN 'COKE'
      WHEN [COMPLEX-LOGIC-2] THEN 'PEPSI'
      WHEN [COMPLEX-LOGIC-3] THEN 'BLOOD'
    END AS [DECISION-MAKER]
    FROM [WHEREVER]
  )

I believe that the technical term for such poor code is "stringly typed". The key issue in the above code is that decisions are being made based on a string output that the developer needs to type and consistently get correct. If anything goes wrong, the language will be incapable of throwing errors. In a traditional language, the workaround would be to construct some sort of dictionary to handle these cases. What is the idiomatic solution in T-SQL? I don't like the idea of making a use-once table, but maybe a temp table would be helpful?

CodePudding user response：

I wouldn't worry about having a "Use in one place" table. If you still worry about it cluttering up your database, you can use a table variable.

DECLARE @AssignmentAndReason AS TABLE ( 
    DECISION-MAKER varchar, 
    EMPLOYEE-ASSIGNMENT varchar, 
    ASSIGNMENT-REASON varchar 
);

INSERT INTO @AssignmentAndReason VALUES (
    ('COKE', 'GIVE COKE', 'Employee prefers coke. Give coke.'),
    ('PEPSI', 'GIVE DEATH', 'Employee prefers pepsi. Give death.'),
    ('BLOOD', 'GIVE DEATH', 'Employee is some sort of vampire. Give death.'),
);

SELECT [EMPLOYEE-ASSIGNMENT], [ASSIGNMENT-REASON],
FROM [WHEREVER]
JOIN @AssignmentAndReason ON [DECISION-MAKER] = CASE
  WHEN [COMPLEX-LOGIC-1] THEN 'COKE'
  WHEN [COMPLEX-LOGIC-2] THEN 'PEPSI'
  WHEN [COMPLEX-LOGIC-3] THEN 'BLOOD'
END

CodePudding user response：

uses "with as(query)"

with firstLogic as
(
   select PersonID, Name, 
   CASE
      WHEN valueDRINK=1 or valueDRINK=2 THEN 'COKE'
      WHEN valueDRINK=3 or valueDRINK=4 THEN 'PEPSI'
      WHEN valueDRINK=5 THEN 'BLOOD'
    END AS [DECISION-MAKER]
   from Persons
   where country='co'
)
select PersonID, Name ,[DECISION-MAKER],
  CASE
    WHEN [DECISION-MAKER] = 'COKE' THEN 'GIVE COKE'
    WHEN [DECISION-MAKER] IN ('PEPSI', 'BLOOD') THEN 'GIVE DEATH'
  END AS [EMPLOYEE-ASSIGNMENT],
  CASE [DECISION-MAKER]
    WHEN 'COKE' THEN 'Employee prefers coke. Give coke.'
    WHEN 'PEPSI' THEN 'Employee prefers pepsi. Give death.'
    WHEN 'BLOOD' THEN 'Employee is some sort of vampire. Give death.'
   END AS [ASSIGNMENT-REASON]
from firstLogic

"with" allows you to have different queries and then join them without creating a temporary table

you can see it in this example