0

Is there a smart way to count how many variables equal some target value (in SQL Server)?

For example, if I have:

declare @a int = 1;
declare @b int = 2;
declare @c int = 1;
declare @target int = 1;

Then I'd like to do how many of @a, @b, @c eqauls @target.

In imperative languages, it's easy to make an inline array of the variables, and then count them - for example in JS:

var a = 1, b = 2, c = 2, target = 1;
if ([a, b, c].filter(item => item == target).length == 1)
   // do something 

The equivalent in SQL of that "inline array" would be a single column table, but this would require using DECLARE TABLE, which I'd like to avoid.

Is there a similarly easy method for such counting in SQL?

Please note I'm less interested in "creating a table without declaring it" - what I really care about is the counting of variables against a target variable, so if it can be done without using tables at all, then it would be better.

2
  • 1
    "but this would require using DECLARE TABLE, which I'd like to avoid" - Why?...SQL is a relational language and a table is a relation, that is the correct tool for the job. While variables are available for use, heavy dependence on them leads to using SQL like a functional language which is not its ideal use. Without using something like a table, I don't think there's a way to accomplish your goal unless you went to some crazy lengths.
    – J.D.
    May 11 at 11:58
  • Is there a reason for the need of not using a table?
    – Ronaldo
    May 11 at 12:36

1 Answer 1

2

How about this solution?

declare @a int = 1;
declare @b int = 2;
declare @c int = 1;
declare @target int = 1;

SELECT COUNT(*)
FROM (VALUES (@a), (@b), (@c)) AS t(_value)
WHERE t._value = @target
New contributor
Max is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • 1
    Exactly what I was looking for, thanks
    – HeyJude
    May 14 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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