#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2

    How to make User defined function parameters optional in sql


    is there any way to make your sql parameters optional esp in a UDF(User Defined Function)?

    i have a query which is this


    declare @employeeID int = null,
    declare @firstName varchar(255) = null,
    declare @lastName varchar(255) = null
    as

    select *
    from dbo.employees
    where (id = @employeeID)
    and
    (firstName = @firstName)
    and
    (lastName = @lastName)

    but if i leave a blank space for the said query, it returns an error which reads
    "An insufficient number of arguments were supplied for the procedure or function."

    any ideas?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    try that

    Code:
    declare @employeeID int = null,
    declare @firstName varchar(255) = null,
    declare @lastName varchar(255) = null
    as
    
    select *
    from dbo.employees
    where  'y' = when @employeeID is null or @employeeID = null then 'y'
             when @employeeID = id then 'y' 
        else 'n' end
    and
    'y' = when @firstName is null or @firstName = null  then 'y'
             when @firstName= firstName then 'y' 
        else 'n' end
    and
    'y' = when @lastName is null or @lastName = null then 'y'
             when @lastName= lastName then 'y' 
           else 'n' end
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by gk53
    when @employeeID is null or @employeeID = null
    that's never going to be true, so why bother with it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    as I remember on SQL 2000 and before @employeeID = null could be true, but if he was SQl 2008 just remove...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by r937
    that's never going to be true, so why bother with it
    Sir, why is it so? could you please explain sir? this could be a new learning for us.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by narrokk
    Sir, why is it so? could you please explain sir? this could be a new learning for us.
    nothing is equal to NULL... not even another NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by r937
    nothing is equal to NULL... not even another NULL
    Ok, which means parameters of a UDF CANNOT BE OPTIONAL...

    Yep...

IMN logo majestic logo threadwatch logo seochat tools logo