Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: I need help with stored procs and UDF

    First off, this is a cross post which is also located here:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073

    Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.

    What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.

    The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.

    The code is below, just copy / past, execute and you will see exactly what I mean.

    Any thoughts?

    Code:
    USE NORTHWIND 
    GO
    
    CREATE FUNCTION RECTANGULAR_XSECTION
    (@fWidth float, @fHeight float)
    
    RETURNS float
    
    AS
    BEGIN
    RETURN (@fWidth * @fHeight)
    END
    GO
    
    
    CREATE PROCEDURE usp_shapes_GetRectangularXSection
    
    @fResult float OUTPUT
    
    AS
    
    declare @fWidth float, @fHeight float
    SELECT @fWidth = 108, @fHeight = 10
    
    SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
    SELECT @fResult AS CalledProcedureOkHere
    
    GO
    
    CREATE PROCEDURE usp_shapes_GetXSection
    
    @fResult float OUTPUT
    
    AS
    
    EXECUTE usp_shapes_GetRectangularXSection @fResult
    SELECT @fResult AS CallingProcedure_NULL?
    GO
    
    declare @fResult float
    EXECUTE usp_shapes_GetXSection @fResult
    SELECT @fResult as OutsideCallingProcedure_NULL?
    GO
    
    DROP FUNCTION RECTANGULAR_XSECTION
    GO
    DROP PROCEDURE usp_shapes_GetRectangularXSection
    GO
    DROP PROCEDURE usp_shapes_GetXSection
    GO
    Mike B

  2. #2
    Join Date
    Feb 2004
    Posts
    134
    I found it, I forgot "OUTPUT" on the call to the stored procedure!!

    Damn, simple, simple, simple!!!!
    Mike B

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [homer]
    dooh
    [/homer]

    I hate when that happens.....

    It was a good post though...

    Sorry I'm late....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by Brett Kaiser
    [homer]
    dooh
    [/homer]

    I hate when that happens.....

    It was a good post though...

    Sorry I'm late....
    Thanks for you response all the same. I know you would have helped

    Mike B

Posting Permissions

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