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

    Join Date
    Aug 2011
    Posts
    6
    Rep Power
    0

    Pls check my SQL Query


    I have a table with fields : EID, EName, HomePH, MobilePH, MobilePH2.

    I write the following statements in order to get Phone Number.
    Pls, anyone could check whether if true or not.

    Create Function udf_PH (@Name varchar (35))
    Returns varchar (100)
    As
    Begin
    Declare @PH varchar (100)
    If HomePH is not null
    Select @PH=@Name +'Home Phone Number is ' + HomePH
    From Employee
    Else if MobilePH is not null
    Select @PH=@Name +'Mobile Phone Number is ' + MobilePH
    From Employee
    Else MobilePH2 is not null
    Select @PH=@Name +'Mobile Phone Number is ' + MobilePH2
    From Employee

    where Ename=@Name
    Return @PH
    End
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    What happened when you tried it?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    46
    Rep Power
    5

    Stored Procedure


    Try the folowing statement:


    Code:
    Create Function udf_PH (@Name varchar (35))
    Returns varchar (100)
    As
    Begin
    Declare @PH varchar (100)
    If HomePH is not null
    Select eName +'Home Phone Number is ' + HomePH
    From Employee where ename=@Name
    Else if MobilePH is not null
    Select eName +'Mobile Phone Number is ' + MobilePH
    From Employee where ename=@Name
    Else MobilePH2 is not null
    Select eName +'Mobile Phone Number is ' + MobilePH2
    From Employee where ename=@Name
    
    where Ename=@Name
    Return @PH
    End
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    6
    Rep Power
    0
    After executing query, two error message is pop up.

    Msg 102, Level 15, State 1, Procedure udf_PH, Line 12
    Incorrect syntax near 'MobilePH2'.
    Msg 156, Level 15, State 1, Procedure udf_PH, Line 16
    Incorrect syntax near the keyword 'where'.

    The second error message is disappear after removing last 'where clause'.

    But still remain the first one!

IMN logo majestic logo threadwatch logo seochat tools logo