Can UDF accept Optional parameter

• Niladri Kumar Saha

  SSC Veteran

  Points: 234

  More actions

  July 14, 2005 at 12:03 am

  #164319

  Hi All,

           I have created a function with optional parameter as we use in Sp.

  Create function dbo.ufn_test(@col1 int =NULL)

  returns int

  as

  begin

  return 1

  end

  To execute the function I am executing the following code

  declare @a int

  select @a =dbo.ufn_test()

  select @a

  I am getting the following error

  Server: Msg 313, Level 16, State 2, Line 2

  An insufficient number of arguments were supplied for the procedure or function dbo.ufn_test.

  but when i am executing the above bold line like the following

  select @a =dbo.ufn_test(NULL)

  i am not getting any error

  if i make this function as Sp i don't need to pass parameter.

  I can execute the sp like the following

  exec sp_test

  so is it not possible to call the function with parameter if that is optional.

  Thanks & Regards

  Niladri

   

   

  ---

  Thanks & Regards,

  Niladri Kumar Saha

• Bert De Haes

  Hall of Fame

  Points: 3093

  More actions

  July 14, 2005 at 11:53 pm

  #574671

  For a function, you have to give all the parameters. You can use default to specify you want to use the default value : select @a =dbo.ufn_test(DEFAULT)

  Bert