SQL Server Performance

Passing Default Value to A parameter in Function

Discussion in 'Getting Started' started by jagblue, May 17, 2007.

  1. jagblue New Member


    Hi I am trying to create a new function in SQL Server 2005 Enterprise edision
    and passing two parameter
    Create Function f(
    @Type INT = 0,
    @TargetDate datetime = NULL)
    RETURNS @result table
    (
    i int,
    Name Varchar(20)
    )

    When i run this funstion Like this

    SELECT * FROM f

    It gives me error (Parameters were not supplied for the function)

    Ane halp
    Thank You
  2. ndinakar Member

    Is that your function definition or you didnt post the code here? Were you able to create the function without any errors?

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. jagblue New Member

    Create Function f(
    @Type INT = 0,
    @TargetDate datetime = NULL)
    RETURNS @result table
    (
    i int,
    Name Varchar(20)
    )
    AS
    BEGIN
    SELECT ID,Name FROM tblAgent
    WHERE AgentType = @Type
    AND ApplicationDate = @TargetDate
    END
  4. ndinakar Member

    did you compile the function without errors?

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  5. jagblue New Member

  6. ndinakar Member

    I dont think there is any version of SQL Server that will compile the code you have as-is.
    (1) You cannot return data via a SELECT statement like you have in the function
    (2) There is no RETURN statement.

    You might want to convert this to a stored proc instead.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  7. Madhivanan Moderator

    <<

    SELECT * FROM f

    >>

    You need to pass values to function's parameters

    Madhivanan

    Failing to plan is Planning to fail
  8. jagblue New Member

    Create Function f(
    @Type INT = 0,
    @TargetDate datetime = NULL)
    RETURNS @result table
    (
    i int,
    Name Varchar(20)
    )
    AS
    BEGIN
    INSERT INTO @Result
    SELECT ID,Name FROM tblAgent
    WHERE AgentType = @Type
    AND ApplicationDate = @TargetDate
    RETURN
    END

    So if i use this function like this
    SELECT * FROM f
    then both parameter values should be default value
    @Type INT = 0
    AND @TargetDate datetime = NULL

    But it gives me this error

    Parameters were not supplied for the function

    Any help
    Thank You
  9. MohammedU New Member

    When a parameter of the function has a default value, the keyword "default" must be specified when calling the function in order to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value.

    select * from f (default,default)


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  10. jagblue New Member

    Thanks
    It worked
  11. MohammedU New Member

    For most of our issues BOL is the best solution because researching anywhere else [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  12. Madhivanan Moderator

    Also BOL is Books On Line, sql server help file [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page