Executing a scalar in T-SQL

Question

What is the best way to accomplish the equivalent of executing scalar in T-SQL? In other words, using SQL, how can I get the first column of the first row of the first result set when calling a stored procedure?

Edit: Just for clarification, this is only using SQL, not a client-side language. The result set could be something like this:

Id Description
--------------
1  foo
2  bar

I need only "1" in the variable.

The only way I know how to do this is to create a temporary table (untested, but you get this idea):

INSERT #TempTable EXEC MyStoredProcedure

DECLARE @id INT;

SELECT TOP(1) @id = Id FROM #TempTable

Answer 1

.net-, SQLCommand.ExecuteScalar.

, , .

EDIT: , :

-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
 exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
 @srvproduct = N'',
 @provider = N'SQLOLEDB', 
 @datasrc = @@servername
go

select top 1 * @id = id 
from openquery(loopback, 'exec MyStoredProcedure') 
go

, ,

Answer 2

, .

SQL

Answer 3

, , , . =)

TOP 1 nameOfFirstColumnReturnedBySP 1 dbo.MyStoredProcedure

№ 1

-- Assuming this temporary table represents the data structure returned by the SP.
CREATE TABLE @Data (
    Column1 int
    , Column2 nvarchar(10)
)

insert into @Data
    exec dbo.MyStoredProcudure

select TOP 1 Column1
    from @Data

: :

, ! =)