Select multiple columns into multiple variables

sql

select-into

data-type

error-handling

byNikita Barsukov·Dec 24, 2024

To directly assign data from multiple columns to variables, follow this SQL structure:

DECLARE @Var1 DataType, @Var2 DataType;  -- (Note: replace DataType with the actual data type of your columns)
SELECT @Var1 = Column1, @Var2 = Column2 FROM YourTable WHERE YourCondition;

Ensure your variable DataType is consistent with the columns you're selecting from, and modify YourCondition to return a single row. For multiple rows, you might need cursors or aggregation functions. This approach assigns Column1 and Column2 values directly to @Var1 and @Var2, simplifying single-row processing.

Select-into mechanics and variations

When using the select-into command, the number of variables in the INTO clause must correspond to the number of columns. It's an efficient way to assign values from specific columns to variables in PL/SQL.

PL/SQL specifics

In PL/SQL, we use the following INTO syntax:

DECLARE
  col1_value TABLE_NAME.COLUMN1%TYPE;  -- Not sure what's the type? Let's abstract it away! :)
  col2_value TABLE_NAME.COLUMN2%TYPE;
BEGIN
  SELECT Column1, Column2 
  INTO col1_value, col2_value
  FROM TABLE_NAME
  WHERE CONDITION;  -- Drake says, "no new records," unless the condition is met
END;

In settings outside of PL/SQL, like SQL*Plus or client applications, variables are prefixed with a colon (:):

SELECT col1, col2 INTO :col1_variable, :col2_variable FROM your_table; -- With a colon so it's cooler.

This syntax allows for assignment in SQL sessions.

Data processing efficiency galore

Using a single statement for data retrieval is both resource-friendly and efficient. It's a practical way to handle data processing in PL/SQL.

Running with aliases

In non-PL/SQL environments, like SQL Server or MySQL, we can use aliases:

-- SQL Server Example
DECLARE @Var1 AS DataType, @Var2 AS DataType;  -- Playing the alias game!
SELECT @Var1 = Column1, @Var2 = Column2 FROM YourTable;

-- MySQL Example
SET @Var1 := (SELECT Column1 FROM YourTable LIMIT 1);  -- MySQL playing hard to get with LIMIT!
SET @Var2 := (SELECT Column2 FROM YourTable LIMIT 1);

These examples show the adaptability of the pattern across different SQL environments.

Dealing with potential hurdles

Embracing this method can also come with its challenges. Let's pre-empt these to save headaches:

One vs Many Rows

When expecting a single-row result but realizing that multiple rows are possible, employ strategies like TOP 1 in SQL Server, LIMIT in MySQL, or row-specific structures in PL/SQL. If not, you may just find an error or, even worse, an unwanted value from the last processed row.

Data Type Dance

Mismatched data types can cause conversion errors. Make sure your DataType matches the corresponding column.

Nullified data

Columns with NULL values need variables that can handle the void. Functions like ISNULL or COALESCE in SQL Server, or NVL in PL/SQL, come to the rescue by providing default values when needed.