question

I think we need more info, are you trying to re-use an existing script but you don't need to use everything it returns? Or are you using a stored procedure/function?

---Below is the MAIN query (join 3-4 different tables)
---Out of this MAIN query I am trying to pull very few fields
---so for example; Select 'CoCode'  FROM  MAIN query

SELECT be.[FY],be.[SAP Doc Number],
CONVERT(NVARCHAR(10),be.[Due Date],101) as 'Due Date',
CONVERT(nvarchar(10),ft.[EndDate],101) as 'End Date', 
DATEDIFF (DAY, ft.[EndDate],be.[Due Date])as Aged,
be.[SAP Customer ID],
be.[SAP Customer Name],mpc.[New_Div],mpc.[BU],mpc.[Company FY15] as 'CoCode', 
sum(be.[Invoice Amount]) 'Total Amount',
sum(be.[Current]) as 'Current',
sum(be.[Aged 1-30]) as '1-30',
sum(be.[Aged 31-60]) as '31-60',
sum(be.[Aged 61-90]) as '61-90',
sum (be.[Aged 91-120]) as '91-120',
sum (be.[Over 120]) as 'Over 120',
ISNULL(mrc.[Long Text],'') as 'RC category',ISNULL(mrc.Category,'') as 'Category'
FROM ([dbo].[FiscalDateTable] as ft
INNER JOIN ([dbo].[TB_DATA_All] as be
LEFT JOIN [dbo].[Master Reason Codes List] AS mrc
ON be.[Reason Code] = mrc.[Reason Code])
ON ft.[Fiscal YYYY-MM] = be.FY)
INNER JOIN [dbo].[Master Profit Center List_All Divisions_New] as mpc
ON be.[Profit Center] = mpc.[Profit Centers]
AND be.CoCd = mpc.[Company FY15]
WHERE (be.[Reconciliation Account]) LIKE'%24%' 
GROUP BY be.[FY], be.[SAP Doc Number],be.[Due Date],ft.EndDate,be.[SAP Customer ID],
be.[SAP Customer Name],mpc.New_Div,mpc.BU,mpc.[Company FY15],
mrc.[Long Text],mrc.Category
HAVING mpc.New_Div ='DI'
ORDER BY [SAP Doc Number]

---I created a SP but have no idea how to pull a field from the SP

USE [CCS]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[spMAIN]
SELECT'Return Value' = @return_value
GO