2
\$\begingroup\$

Context

I was proud of this code for a little while, but the repetition wounds me. I know that the function name sucks, but the name and interface to this function are not under my control. Performance is not a concern. Note that this must be an inline table-valued function.

Fixing the repetition is my only concern. How can I reduce it?

This is a follow-up from this question. You do not and should not need to read that question first. This question exists because I oversimplified that one.

Dependencies

CREATE TABLE [WINNERS]
(
  WINNER_ID INT NOT NULL
  AMOUNT INT NOT NULL
);

CREATE TABLE [RelevantWinners]
(
  WINNER_ID INT NOT NULL
);

INSERT INTO [WINNERS]
  VALUES (1, 20), (2, 30), (3, 309), (4, 50), (5, 50);

INSERT INTO [RelevantWinners]
  VALUES (1), (5);

The Code For Review

CREATE FUNCTION RecentWinners
(
  @GetAllWinners BIT
)
RETURNS TABLE
AS
RETURN
(
-- Style note: I've yet to figure out how to best indent consecutive CTEs.
WITH
  [WinnersRelevant] AS
  (
    SELECT
      -- The inclusion of a silly calculation is deliberate.  
      2 * [Win].[AMOUNT] + 47 [ENCODING]
    FROM
      [Winners] [Win]
    WHERE EXISTS
      (
          SELECT 1
          FROM [RelevantWinners] [Rev]
          WHERE [Rev].[WINNER_ID] = [Win].[WINNER_ID]
      )
  ),
  [WinnersAll] AS
  (
      -- This is the first major repetition.
      -- It's exactly the same code as before, but without the semi-join.
    SELECT
      2 * [Win].[AMOUNT] + 47 [ENCODING]
    FROM
      [Winners] [Win]
  )
    SELECT
      [ENCODING],
      -- The inclusion of a second silly calculation is deliberate.
      2 * [ENCODING] + 12346 [ENCODING_2]
    FROM
      [WinnersRelevant]
    WHERE
      @GetAllWinners = 0

 UNION ALL
      -- This is the second major repetition.
      -- It's exactly the same SELECT list as before.
    SELECT
      [ENCODING],
      2 * [ENCODING] + 12346 [ENCODING_2]
    FROM 
      [WinnersAll]
    WHERE
      @GetAllWinners = 1
); --I'm pretty sure this is the only relevant semicolon, even under strict standards.

DB Fiddle.

\$\endgroup\$
0

1 Answer 1

2
\$\begingroup\$

Just lump another sub-predicate beside your exists:

create function RecentWinners(@GetAllWinners bit)
returns table
as return (
  select encoding,
         2*encoding + 12346 as encoding_2
  from (
    select 2*amount + 47 as encoding
    from Winners
    where @GetAllWinners=1 or exists (
      select 1 from RelevantWinners as rw
      where rw.winner_id = Winners.winner_id
    )
  ) as WinnersAggregate
);

I don't care much for shouting, hence the lowercase.

\$\endgroup\$
4
  • \$\begingroup\$ Thanks. I'll inspect this closer later on. Are you sure that exists is needed? Since only one column is being looked at, I reckon that IN would give a shorter query. \$\endgroup\$
    – J. Mini
    Oct 4, 2023 at 11:15
  • \$\begingroup\$ @J.Mini read about the performance trade-offs. I consider exists to be idiomatic, and any change away from it strictly for the purposes of query code length would have negligible impact. The performance characteristics have received mixed descriptions over the years but the differences are likely to be minimal. \$\endgroup\$
    – Reinderien
    Oct 4, 2023 at 12:50
  • \$\begingroup\$ Yes! What a fool I was for writing the original! \$\endgroup\$
    – J. Mini
    Oct 20, 2023 at 8:27
  • \$\begingroup\$ @J. Mini please don't call yourself names. We all evolve one step at a time. And I certainly have seen way worse than your code. \$\endgroup\$ Nov 3, 2023 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.