Getting values from a SP which is called inside an Sp in SQL Server 2008

I am having an SP[A] which is calling another SP[B] which will is a select query and it returns one row with so many columns.How can I get the value of a particular column of the first called SP开发者_运维知识库 (ie B). Is there any way without using hash tables ?

You either need to use temp tables to store the result into, or convert SP[B] into a table valued function which you can then call inline.

e.g.

CREATE FUNCTION dbo.FxnB(@Id INTEGER)
RETURNS TABLE
AS
RETURN
(
SELECT FieldA, FieldB, FieldC
FROM SomeTable
WHERE ID = @Id
)

-- Then use it like this
DECLARE @FieldA VARCHAR(50)
SELECT @FieldA = FieldA FROM dbo.FxnB(1)