Sproc TSQL Syntax Help

Hi All,

I need some advice on TSQL syntax. Please take a look at the line below, which forma part of a Stored procedure.
---------------------------------------------------
, IsNull(IsNull(' (M)' + Mx_Student_cell1,'') +  IsNull(' (F)' + Mx_Student_cell2,''), Mx_Student_TEL1)
---------------------------------------------------

What I am trying to get is the following.
1.      Collect ‘Mx_Student_cell1’ and if it’s Not Null append (M), otherwise return nothing.
2. Collect ‘Mx_Student_cell2’ and if it’s Not Null append (F), otherwise return nothing.

3. If both 1 and 2 return nothing (Null) only then collect Mx_Student_TEL1

I hope I am being clear enough.

Kind regards

A M de Groot Sanders

PS running on MS SQL server 2012 or at least 2008R2
LVL 5
Abiel de GrootDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
CASE
    WHEN Mx_Student_cell1 IS NULL AND Mx_Student_cell2 IS NULL THEN Mx_Student_TEL1
    ELSE CASE
            WHEN Mx_Student_cell1 IS NOT NULL THEN 'M'
            ELSE ''
        END 
        +
        CASE
            WHEN Mx_Student_cell2 IS NOT NULL THEN 'F'
            ELSE ''
        END
END

Open in new window

0
Abiel de GrootDeveloperAuthor Commented:
Hi Vitor,

No, that's returning MF for everything except where Mx_Student_cell1 & Mx_Student_cell2 are null

Thanks for your help

A
0
Athar SyedCommented:
ISNULL(Mx_Student_cell1 + 'M', ISNULL(Mx_Student_cell2 + 'F', Mx_Student_Tel1))

Open in new window


Readability is better as below

ISNULL('M: ' + Mx_Student_cell1, ISNULL('F: ' + Mx_Student_cell2, ISNULL('T: ' + Mx_Student_Tel1, 'No Contact'))) AS [Contact Number]

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
No, that's returning MF for everything except where Mx_Student_cell1 & Mx_Student_cell2 are null
It's really this that I understood from your question. Which more options that it has?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, just re read your question:
CASE
    WHEN Mx_Student_cell1 IS NULL AND Mx_Student_cell2 IS NULL THEN Mx_Student_TEL1
    ELSE CASE
            WHEN Mx_Student_cell1 IS NOT NULL THEN '(M)'+Mx_Student_cell1
            ELSE ''
        END 
        +
        CASE
            WHEN Mx_Student_cell2 IS NOT NULL THEN '(F)'+Mx_Student_cell2
            ELSE ''
        END
END

Open in new window

0
Abiel de GrootDeveloperAuthor Commented:
There is always a value in either:
In (a)[‘Mx_Student_cell1’ or ‘Mx_Student_cell2’] or in  (b)[‘Mx_Student_TEL1’]

If there is a value in (a) Then append the (M) and (F) otherwise collect option (b)
Many thanks to you both.

A
0
Abiel de GrootDeveloperAuthor Commented:
Almost perfect Vitor!

Just one small thing. Sometimes there is no value in 'Mx_Student_cell2' but it returns an (F). I only want it to append the (F) or (M) if there is a value in their respective fields.

Many thanks.

A
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sometimes there is no value in 'Mx_Student_cell2' but it returns an (F). I only want it to append the (F) or (M) if there is a value in their respective fields.
That means it's accepting also empty string ('') so we need to test for empty string and NULL value as well:
CASE
    WHEN Mx_Student_cell1 IS NULL AND Mx_Student_cell2 IS NULL THEN Mx_Student_TEL1
    ELSE CASE
            WHEN ISNULL(Mx_Student_cell1,'')='' THEN ''
            ELSE '(M)'+Mx_Student_cell1
        END 
        +
        CASE
            WHEN ISNULL(Mx_Student_cell2,'')='' THEN ''
            ELSE '(F)'+Mx_Student_cell2
        END
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abiel de GrootDeveloperAuthor Commented:
Actually, I have just had a look and the script is behaving as you said. The fact that sometimes the field is not NULL but simply does not have a value. (Not the same thing) Is there something I can add like Len() or something to take account of this?

Sorry.

A
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, there's a LEN() function but my last script should take care of those cases since I'm testing if the value it's equal to empty string (''). Did you test it?
0
Abiel de GrootDeveloperAuthor Commented:
That's perfect Vitor ;-)

many thanks

A.

Ps. I am going to ask another very simply Syntax based question right now. It should be right up your street.

I will close this and award you the points
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.