asked on

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

Vitor Montalvão

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

Abiel de Groot

ASKER

Hi Vitor,

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

Thanks for your help

A

Athar Syed

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

Vitor Montalvão

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?

Vitor Montalvão

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

Abiel de Groot

ASKER

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

Abiel de Groot

ASKER

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abiel de Groot

ASKER

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

Vitor Montalvão

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?

Abiel de Groot

ASKER

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