Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Optional argument in UDF?

    Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Create proc proc_name
    @param Integer = default_value
    Last edited by r123456; 01-27-04 at 12:26.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I"m running into a bit of an error with that one. First off it is a UDF, so I'm creating a function instead of a proc. The type for the field is datetime, so I'm trying this:

    @myDate datetime = null AS datetime

    and

    @myDate datetime = '1/1/1900' AS datetime

    both of them produce an "Incorrect syntax near the keyword 'AS'" error.

    What's the correct syntax for this declaration?

    Edit:

    I also tried:

    @myDate datetime = '1/1/1900'

    This compiled correctly, but still requires the argument when calling the udf from a query. I can send an empty string and return the default value, but that defeats the purpose of making the argument optional, as I would still have to go through all of the queries I've already used this udf in and supply empty strings for the third argument... Thanks for the hint.. is there anything else I can do here?

    Basically I want to create an overload function for this udf.. of course sql server doesn't play nice having two functions of the same name.. so I'm not sure how to do this.
    Last edited by Teddy; 01-27-04 at 12:46.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Like:

    Code:
    CREATE FUNCTION myFunction99 (@ToBeOrNotToBe int)
    RETURNS int
    AS
      BEGIN
    	DECLARE @x int
    	SELECT @x = CASE WHEN @ToBeOrNotToBe IS NULL THEN 0 ELSE @ToBeOrNotToBe END
    	RETURN @x
      END
    GO
    
    DECLARE @y int
    SELECT dbo.myFunction99(@y)
    SELECT @y = 5
    SELECT dbo.myFunction99(@y)
    GO
    
    DROP FUNCTION myFunction99
    GO
    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.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's close, but in that example, I would need it to support:

    dbo.myFunction99()

    The udf that I'm using is roughly of the syntax:

    dbo.myFunction(aDate, aFloat, anOptionalDate)

    I need it to support:

    dbo.myFunction('1/1/2003', 1200, '6/30/2003')

    and (of course this where the issue is)

    dbo.myFunction('1/1/2003', 1200)

    As is, the udf supports

    dbo.myFunction('1/1/2003', 1200, '')

    with no hitch. I just don't know how to make the third argument entirely optional

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, I don't think you can....but aren't you using local variable in the udf call? Not hard coded values? If so, you can make the third one NULL like in my example...No?
    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.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Brett Kaiser
    Well, I don't think you can....but aren't you using local variable in the udf call? Not hard coded values? If so, you can make the third one NULL like in my example...No?
    Nope, all the arguments to the udf are supplied from a record in the select statement.. usage is:

    SELECT t1.Cash, t1.Date, dbo.myFunction(t1.Cash, t1.Date)

    The problem is the function calculates a financial figure based on a date range. For most of the records, the end date for this range can be derived from t1.Date. However, there are a few exceptions where the end date must actually be supplied because of a premature termination. So I need to account for this possibility:

    SELECT t1.Cash, t1.Date, dbo.myFunction(t1.Cash, t1.Date, t1.termDate)

    As previously mentioned, essentially I need to find a way to overload the function. So far research is not looking good.

    Is there perhaps a way to trap errors when calling the function?

    Last edited by Teddy; 01-27-04 at 13:18.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    nah...it'll just raise

    Code:
    CREATE FUNCTION myFunction99 (@ToBeOrNotToBe int)
    RETURNS int
    AS
      BEGIN
    	DECLARE @x int
    	SELECT @x = CASE WHEN @ToBeOrNotToBe IS NULL THEN 0 ELSE @ToBeOrNotToBe END
    	RETURN @x
      END
    GO
    
    DECLARE @y int
    SELECT dbo.myFunction99(@y)
    SELECT @y = 5
    SELECT dbo.myFunction99(@y)
    SELECT dbo.myFunction99()
    SELECT @@ERROR
    GO
    
    DROP FUNCTION myFunction99
    GO
    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.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So I came up with a workaround. Basically now I'm sending a comma deliminated string and parsing the two dates out of it.

    I declared two local variables within the udf and I assign them values based on whether or not there are two valid dates included in the original string. Works pretty slick. A bit messy for my taste, but functional.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Teddy
    So I came up with a workaround. Basically now I'm sending a comma deliminated string and parsing the two dates out of it.

    I declared two local variables within the udf and I assign them values based on whether or not there are two valid dates included in the original string. Works pretty slick. A bit messy for my taste, but functional.
    ....and you're nopt gonna share? 8-(
    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.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Brett Kaiser
    ....and you're nopt gonna share? 8-(
    I was just waiting for you to ask. It's no fun if you can't make 'em beg.


    Code:
    CREATE FUNCTION dbo.myFunction 
    (
    @strDates AS varchar(50),
    @cash AS money
    )
    
    RETURNS money
    AS 
    
    BEGIN
    
    DECLARE @x AS money
    DECLARE @sDate AS datetime
    DECLARE @eDate AS datetime
    
    IF  (LEN(@strDates) - CHARINDEX(',', @strDates, 0)) > 0 AND CHARINDEX(',', @strDates, 0) > 0
    	BEGIN
    		SELECT @sDate = LEFT(@strDates, CHARINDEX(',', @strDates, 0) - 1)
    		SELECT @eDate = RIGHT(@strDates, LEN(@strDates) - CHARINDEX(',', @strDates, 0))
    	END
    ELSE
    BEGIN
    	IF CHARINDEX(',', @strDates, 0) > 0
    		BEGIN
    			SELECT @sDate = LEFT(@strDates, (LEN(@strDates) - 1))
    		END
    	ELSE
    		BEGIN
    			SELECT @sDate = LEFT(@strDates, LEN(@strDates))
    		END
    	
    	SELECT @eDate =  '1/1/' + str(YEAR(@sDate) + 1)
    END
    		
    
    SELECT @x = some stuff based on sDate, eDate and another UDF.
    
    RETURN @x
    
    END
    Last edited by Teddy; 01-27-04 at 16:05.

Posting Permissions

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