Sql SELECT multi-columns INTO multi-variable

sql

sql-scripts

variable-assignment

null-handling

byAlex Kataev·Sep 9, 2024

To extract multiple columns into separate variables in SQL Server (T-SQL), we use a slight twist on the usual SELECT statement:

-- Could be a start of a beautiful friendship, or at least a neat SQL script
DECLARE @Var1 DataType, @Var2 DataType; 

-- Here we are... Indulging in a little SQL magic
SELECT @Var1 = Col1, @Var2 = Col2 FROM Table WHERE Condition;

In PL/SQL (Oracle), we use the INTO clause to achieve this:

DECLARE
  -- Don't 'declare' too loudly, it's just variable initialization
  Var1 DataType;
  Var2 DataType;
BEGIN
  -- Here goes nothing... or something, depends on your data, really!
  SELECT Col1, Col2 INTO Var1, Var2 FROM Table WHERE Condition;
END;

In both approaches, substitute DataType with your desired data type, Col1 and Col2 with your column names, Table with your table name, and Condition with your filter condition.

Translating from Teradata to SQL Server

Converting from Teradata? Here's how you use the same pattern in SQL Server:

-- Teradata syntax, it's like ordering a pizza with pineapple
SELECT Col1, Col2 INTO :Var1, :Var2 FROM Table WHERE Condition;

-- SQL Server equivalent, more traditional like pepperoni pizza
DECLARE @Var1 DataType, @Var2 DataType;
SELECT @Var1 = Col1, @Var2 = Col2 FROM Table WHERE Condition;

This way, you can remain calm when switching from Teradata to SQL Server. You have the SELECT statement in your tool belt and you're not afraid to use it!

Reasons to SELECT SELECT over SET

Wondering why to use SELECT instead of SET for variable assignment? Here's the rundown:

Multiple Variables: SELECT lets you juggle (assign) multiple variables at once, whereas SET is more of a one-track mind.
NULL Handling: If the SQL query returns no rows, SELECT simply nods and moves on, leaving your variables untouched. SET, on the other hand, is a drama queen and assigns NULL.
Error Handling: Expecting one row but got multiple? SET panics and throws an error, SELECT shrugs and picks the last value.
Performance: SELECT can be faster than SET, especially when dealing with multiple assignments.

These insights can be a game-changer when writing and optimizing your SQL scripts.

Procedures for NULL and Error Handling

There's more to SQL than meets the eye. Here are some tips and tricks to prevent SQL from turning against you:

Avoiding NULLs: Use the COALESCE function to provide a friendly default value when the big bad NULL shows up.
Handling Multiple Rows: Use TOP 1 with the ORDER BY clause to ensure only one row is returned. Your script will thank you.
Preventing Locks: The NOLOCK hint is your friend when you're only reading data and want to keep your tables "unlock" and sociable.
Safe Transactions: Use transactions when you need to make sure your actions stay together, like a close-knit SQL family.

These tried-and-tested techniques will improve your scripts' reliability and robustness.

Optimizing Queries with Large Data Sets

Working with large data sets? Here are some life savers to keep your script running smoothly:

Batch Processing: Divide and conquer large operations by breaking them into smaller, bite-sized batches.
Indexes: Zipping too many columns in your WHERE clause? Better make sure they are indexed for top-notch performance.
Profiling: Need to identify the culprits slowing your script? Use query profiling tools. They are like Sherlock Holmes, but for SQL.
Temp Tables: Storing results in temporary tables can be more efficient if you're repeatedly using the same data. Like a stash of cookies, but for SQL.

Master these areas, and your SQL scripts will be both precise and ridiculously fast.

explain-codes / Sql / Sql SELECT multi-columns INTO multi-variable

Linked

Sql Server SELECT INTO @variable?



Error SQL70001: This statement is not recognized in this context



Setting two scalar variables in one SELECT statement?



Must declare the scalar variable: *How to Rid Your Code of this Pesky Error*



Mysql SELECT IF statement with OR