Results 1 to 5 of 5

Thread: Tsql case

  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Tsql case

    im 1 day new to sql server and trying to build a full name for a sp
    so i am testing using :

    DECLARE @SAL varchar(4)
    DECLARE @Fname varchar(15)
    DECLARE @MI varchar(10)
    DECLARE @Lname varchar(15)
    DECLARE @Title varchar(25)
    DECLARE @Contact varchar(50)


    SET @SAL = 'Mr.'
    SET @Fname = 'Conrad'
    SET @MI = null
    SET @Lname = 'Bains'
    SET @Title = null

    SELECT @Contact +
    CASE
    WHEN @Sal IS NULL THEN '' + @Fname + ' '
    WHEN @Mi IS NULL THEN + @Lname
    ELSE +@Mi + ' ' + @Lname
    WHEN @Title IS NULL THEN + ''
    ELSE +', ' + @Title
    END

    the else is wrong not sure how to handle any help on my simpleton ?

    THnks

  2. #2
    Join Date
    Oct 2003
    Posts
    311
    DECLARE @SAL varchar(4)
    DECLARE @Fname varchar(15)
    DECLARE @MI varchar(10)
    DECLARE @Lname varchar(15)
    DECLARE @Title varchar(25)
    DECLARE @Contact varchar(50)

    --initialize return values
    SET @SAL = 'Mr.'
    SET @Fname = 'Conrad'
    SET @MI = 'J.'
    SET @Lname = 'Bains'
    SET @Title = 'Super Genius'

    IF NOT @Sal IS NULL
    SET @Contact = @Sal
    IF Len(@Contact) > 1
    SET @Contact = @Contact+ ' ' + @Fname + ' '
    IF NOT @Mi IS NULL
    SET @Contact =@Contact+ @Mi + ' ' + @Lname
    ELSE
    SET @Contact =@Contact+ @Lname
    IF NOT @Title IS NULL
    SET @Contact =@Contact+ ', ' + @Title

    PRINT @CONTACT

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    DECLARE @SAL varchar(4) 
    DECLARE @Fname varchar(15) 
    DECLARE @MI varchar(10) 
    DECLARE @Lname varchar(15) 
    DECLARE @Title varchar(25) 
    DECLARE @Contact varchar(50) 
    
    --initialize return values
    SET @SAL = 'Mr.'
    SET @Fname = 'Conrad'
    SET @MI = 'J.'
    SET @Lname = 'Bains'
    SET @Title = 'Super Genius'
    
    SET @Contact = Coalesce(@Sal, '') + Coalesce(' ' + @Fname, '') 
    +  Coalesce(' ' + @Mi) + Coalesce(' ' + @Lname, '') 
    +  Coalesce(', ' + @Title, '')
    
    PRINT @CONTACT
    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Minor detail:

    SET @Contact = ltrim(Coalesce(@Sal, '') + Coalesce(' ' + @Fname, ''))
    + Coalesce(' ' + @Mi, '') + Coalesce(' ' + @Lname, '')
    + Coalesce(', ' + @Title, '')
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good point!

    -PatP

Posting Permissions

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