Select into 2 variables? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select into 2 variables?

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-11 : 10:25:11

Hi all,

I'm not sure how to select into 2 variables within t-sql.

I have this statement of selecting 1 column.

select @email = email from [server].database.dbo.table1 a
where a.empid = @empid

How can I select from an additional column and store into a second variable? Would I need two selects or can I combine it within this one? Something like this maybe...

select @email,@empnum = email, empnum from [server].database.dbo.table1 a where a.empid = @empid

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-11 : 10:37:46

SELECT @email = email, @empnum = empnum
FROM ....
WHERE...

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-11 : 10:49:07

The T-SQL dialect is:

SELECT @p1= x1, @p2= x2, .., @pn= xn
FROM .. WHERE..

The ANSI Standard syntax which T-SQL seems to want to move toward uses a row constructors:

SET (p1, p2, .., pn) = (SELECT x1, x2, .., xn FROM .. WHERE ..);)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-11 : 13:40:02

Thanks a lot.

One other question. From within such a query, can I parse out the first x number of characters of a field, like..

select @empnum = instr(4, empnum)

It's probably not INSTR but something like this maybe?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-11 : 14:18:34

Sure you can.

SELECT @var = LEFT(myColumn, 4)

SELECT @var = SUBSTRING(myColumn, 1, 4)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources