Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

    Unanswered: 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,

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    you should write your UDF as a scalar function.

    Post your fnTotalJobPrice here.
    -----------------
    KH


  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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,

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.
    Code:
    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

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •