Pass back one value from UDF having found it in a table

How do I pass back only one value, such as TotalJobPrice from the tblJobRecords when I know which JobID to Select. All I want is the TotalJobPrice returned from the UDF, not a record from the table. I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)
Hope that is clear.
Thanks in advance,you should write your UDF as a scalar function.

Post your fnTotalJobPrice here.|||I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)I would like to write a function that would allow me to be able to get the TotalJobPrice out of the function. But I DO NOT KNOW HOW to get one field of data out of one record in a table using T-SQL. I am brand new at T-SQL and would like to know if there is a way to get one field of data out of one record so I can put that data into a local variable. I don't have fnTotalJobPrice to post. I want to learn how to do this one thing, then I feel I can write it. Thanks,|||declare @.i int
select @.i = myintcolumn from mytable where ...

Just make sure that the query returns only one row. if it returns multiple, which value you get is undefined.|||Thank you very much for your input on how to obtain a value from a table within a stored procedure or function. Here is my stored procedure, as I found out I did not need the function after all, but the same coding seems to hold true in both types of objects. I also noticed that I needed the SELECT in the SQL string. Any idea why? I thought is must be because I was not assigning the return of that SQL string to a local variable.ALTER PROC spCalculateTotalBillToDate
@.JobID as int
AS
BEGIN
DECLARE @.TotAmount AS Money
DECLARE @.BillOption AS varchar(3)
DECLARE @.BillCodeID AS int
DECLARE @.BillingStep as int
SET @.TotAmount = 0
SET @.BillCodeID = (SELECT Max(BillCodeID) FROM dbo.tblInvoice
WHERE JobID = @.JobID)
IF ISNULL(@.BillCodeID, ' ') = ' '
Begin
GOTO Return0
END
-- Bill Option
SET @.BillOption = (SELECT BillOption FROM dbo.tblBillOptions
WHERE BillOptionID =
(SELECT BillOptionID FROM dbo.tblProject
WHERE ProjectID =
(SELECT ProjectID FROM dbo.tblJob WHERE JobID = @.JobID)))
SET @.BillingStep = (SELECT max(BillingStep) FROM dbo.tblJobStatus
WHERE JobID = @.JobID AND JobStatusID <=
(SELECT JobStatusID FROM dbo.tblJob WHERE JobID = @.JobID))
--Calculate TotAmount
SET ANSI_WARNINGS OFF
SET @.TotAmount = (SELECT Sum(Amount) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob WHERE JobID = @.JobID)
AND BillCodeID <= @.BillCodeID)
SET ANSI_WARNINGS ON
IF @.BillingStep >= 89
BEGIN
--Print 'BillingStep is >= 89'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END
ELSE
IF @.BillOption = '_'
BEGIN
--Print 'BillOption = underscore'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID,
(SELECT Sum([Percent]) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob
WHERE JobID = @.JobID) AND BillCodeID <= @.BillCodeID))
END
ELSE
IF @.BillOption = 'D'
BEGIN
--Print 'BillOption = "D"'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END

Return0:
--print CAST(@.TotAmount AS VARCHAR)
SELECT @.TotAmount TotAmount
END|||not exactly sure what you are asking. There are several ways to return data from a proc. this article will educate you on all of them:

http://www.sommarskog.se/share_data.html|||What I was asking was about needing to use "SELECT" when obtaining the information from the table. The code you shared with me you did NOT use "SELECT", so I asked if this was different because I was using in within some other SQL, rather than just assigning the data to a local variable. If you notice within the stored procedure I posted, I was able to return what I wanted to the caller.

My original question was not how to return data FROM a function, but how to get data from a table to be used IN a function.

Again, thanks for your help.|||hmm.

in the code I posted for you, i *did* use "select" to get data from a table. I didn't type it in all caps though. sql keywords are not case sensitive. I don't know of any way to get data out of a table other than select...

in any case it sounds like you got the answer you were looking for. :)