How to return multiple variables in SQL server

Returning single variable from a query in SQL server

Before going to learn how to return multiple variables let's look at the returning process of a single variable in an SQL server. It is so easy, you can return just like the function return may already know.

Let's return the total number of customers whose salary is more than 15000

DECLARE @TOTAL_CUSTOMER_15000  AS NUMERIC(18) = 0

SELECT @TOTAL_CUSTOMER_15000  = COUNT(*) FROM CUSTOMERS
WHERE SALARY > 15000

RETURN @TOTAL_CUSTOMER_15000  AS ‘TOTAL_CUSTOMER_15000’

Return multiple variables from a single query in SQL server.

Just like the single variable return process you can return multiple variables. Let's check an example.

Lets return the total number of customers and the total number of customers whose salary is more than 15000

DECLARE @TOTAL_CUSTOMER  AS NUMERIC(18) = 0
DECLARE @TOTAL_CUSTOMER_15000  AS NUMERIC(18) = 0;

SELECT @TOTAL_CUSTOMER  = COUNT(*) FROM CUSTOMERS

SELECT @TOTAL_CUSTOMER_15000  = COUNT(*) FROM CUSTOMERS
WHERE SALARY > 15000

SELECT @TOTAL_CUSTOMER  AS ‘TOTAL_CUSTOMER’,
        @TOTAL_CUSTOMER_15000  AS ‘TOTAL_CUSTOMER_15000’

So like the example you can easily return multiple variables in SQL server.