Sql - Assign multiple variables from query result with condition

Question

Assume the query

SELECT RecordType, COUNT(*) RecordsCount FROM Records AS WHERE ... GROUP BY RecordType returns this table:

------------------------------
|RecordType   | RecordsCount |
------------------------------
|      1       |    15       |
------------------------------
|      2       |    10       |
------------------------------
|      3       |    8        |
------------------------------

I've defined those variables:

DECLARE @paymentRecordCount INT = 0;
DECLARE @dataRecordCount INT = 0;

I'm looking for a way to set at once the both variables - @paymentRecordCount variable to the result of the condition where RecordType = 1, and to set the @dataRecordCount variable to the result of the condition where RecordType = 3.

The only way I've found so far is to compute the select query multiple times something like: select @dataRecordCount = RecordsCount from (select ... from ..) where RecordType = 3 and to do the same for the other variable, like: select @paymentRecordCount = RecordsCount from (select ... from ..) where RecordType = 1

There is a way to compute the query once and set the both varaibles together?

Is the method you have tried in your question causing you any issues? Why is that solution not sufficient? — iamdave
– iamdave, Commented Oct 4, 2018 at 9:20
@iamdave it works, but I want to know if it's the best approach or I can do that calculation at once? — Roni
– Roni, Commented Oct 4, 2018 at 9:24
@Roni It would also help to see the query that gave you the results you show. The reason being that the best approach is to pivot your results to 1 row of 3 columns before assigning them to the variables. — MatBailie
– MatBailie, Commented Oct 4, 2018 at 9:37

MatBailie · Accepted Answer · 2018-10-04 09:47:35Z

2

SELECT
    @paymentRecordCount = SUM(CASE WHEN RecordType = 1 THEN 1 END),
    @dataRecordCount    = SUM(CASE WHEN RecordType = 3 THEN 1 END)
FROM
    Records
WHERE
    ... 
    AND RecordType IN (1, 3)

answered Oct 4, 2018 at 9:47

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vladislav · Accepted Answer · 2018-10-04 10:36:58Z

0

You could use a statement with one subquery per RecordType, but you have to consider the case when (if) there are multiple values per RecordType:

declare @table Table (recordtype int, recordscount int)
insert @table values (1, 1);
declare @dataRecordCount int, @paymentRecordCount int;

SELECT
@paymentRecordCount = (SELECT RecordsCount FROM @table WHERE RecordType=1),
@dataRecordCount = (SELECT RecordsCount FROM @table WHERE RecordType=3);


SELECT @dataRecordCount, @paymentRecordCount;

edited Oct 4, 2018 at 10:36

answered Oct 4, 2018 at 9:22

Vladislav

3,0122 gold badges25 silver badges44 bronze badges

7 Comments

Vladislav Over a year ago

Thanks, I like to explicitly set the results.

Zohar Peled Over a year ago

Actually, that will result with one of the variables as null. It should be ELSE <variableName> END - See rextester demo.

MatBailie Over a year ago

@ZoharPeled Actually, should use SUM(CASE) or MAX(CASE) to collapse everything to a single row before assignment to the variables.

Zohar Peled Over a year ago

@MatBailie Yes, that would probably be better :-)

Roni Over a year ago

@Vladislav Thanks! but it's not working... Just try double insert as insert @table values (1, 8); insert @table values (3, 8); and look in the rersults

|

Collectives™ on Stack Overflow

Sql - Assign multiple variables from query result with condition

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related