[lnkForumImage]
TotalShareware - Download Free Software

Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How to retrieve column value into a variable?

~~~ .NET Ed ~~~

3/31/2007 10:21:00 AM

When I was into Oracle I used to be able to do things like "SELECT column
INTO varname" and then use that variable into subsequent operations. I need
to do something like that in SQL Server 2005 (Express).

I have a table A with a PK and and several other columns. I am interested in
a non-PK column, say COL_B based on a PK and then use that value to select
on other tables. Something like this:

SELECT col_b INTO var FROM tbl_a WHERE pk_col = 12345
SELECT * FROM tbl_b WHERE col_m = @var

well you get the idea, is there a way to do that?


1 Answer

David Portas

3/31/2007 10:52:00 AM

0

On 31 Mar, 11:21, "~~~ .NET Ed ~~~" <tiredofs...@abolishspam.now>
wrote:
> When I was into Oracle I used to be able to do things like "SELECT column
> INTO varname" and then use that variable into subsequent operations. I need
> to do something like that in SQL Server 2005 (Express).
>
> I have a table A with a PK and and several other columns. I am interested in
> a non-PK column, say COL_B based on a PK and then use that value to select
> on other tables. Something like this:
>
> SELECT col_b INTO var FROM tbl_a WHERE pk_col = 12345
> SELECT * FROM tbl_b WHERE col_m = @var
>
> well you get the idea, is there a way to do that?

Why not do this?:

SELECT *
FROM tbl_b
WHERE col_m =
(SELECT col_b
FROM tbl_a
WHERE pk_col = 12345);

Or use a JOIN instead of a subquery. Do NOT use SELECT * in production
code though.

If you need to assign a value to a variable then there are two
methods. Microsoft's recommended syntax is to use SET:

SET @var = (SELECT col FROM ...);

You can also assign variables in a SELECT statement, which has the
advantage of allowing more than one assignment in a single statement.

SELECT @var1 = col1, @var2 = col2 FROM ...

There are a few very important points to understand about the SELECT
syntax. Firstly, it does not return a result set like other SELECT
statements. Secondly, the result may be undefined if the query returns
more than one row, so it usually makes sense to be sure that only one
row can be returned. Thirdly, if no rows are returned then no
assignment happens - the variable retains the value it had before the
assignment statement.

I'll say again that I doubt the example you gave justifies the use of
a variable. In SQL, declarative code is usually preferable to
procedural. That's something that Oracle PL/SQL programmers sometimes
tend to forget in my experience.

HTH

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--