Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    Unanswered: Default for an int parameter in SP

    I'm using a stored procedure that receives one parameter namely @EmployeeID INT

    but when I want to give this parameter a default value, my SP fails.

    I did it like with a varchar where it works.

    ---
    @Employee INT = '%'

    ---

    Is this correct or did I use a wrong syntac/wildcard?

    Greetings,
    Geoff

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    INT is a numeric datatype only you should use digits to assign defaults.
    If not use VARCHAR to assign such special chars.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2004
    Posts
    18

    so...

    Isn't there a numeric default that I can use?

    actually i use the SP to do the next thing.

    If I don't give a parameter I want all recoreds to be returned. When passing through the ID (as parameter tot the SP) I want only that record to be returned. So if I understand correctly you're saying there is no sucth thing as setting a default for an int-type?

    Greetings,
    Godofredo

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Only numerics are allowed as default for int.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Mar 2004
    Posts
    18

    range?

    Can i define a range then?

    like @EmployeeID INT = [0-9]

    so that all numbers can be received? or how precisely do I do this?

    If no parameter is given I want al records returned.

    Greetings,
    Geoff

  6. #6
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    CREATE PROCEDURE sp_myproc
    @emp_id int = 0
    as

    select * from my_table
    where emp_id =
    case @emp_id when 0 then emp_id else @emp_id end

    when you call sp_myproc without specifying any parameters then all employees are retrieved.

    Is that what you were asking?

  7. #7
    Join Date
    Mar 2004
    Posts
    18

    Smile indeed

    Yes indeed

Posting Permissions

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