If else condition in MSSQL

Question

Suppose I have serial number, test name and few other columns, i want to write a condition if TESTNAME is null for a particular serial number then set the TESTNAME to blank else perform inner join

SELECT 
    (A.PTNUMBER + '-' +A.SL_NO) AS ENUMBER, 
    D.ENGINEER AS REQ, D.DATETIME as "DATE",
    (select Value 
     from DROPDOWN 
     where B.TEST_NAME=CONVERT(VARCHAR,DropdownID)) TESTNAME, 
    TABLE_NAME AS TABLETD 
FROM INSPECTION D 
INNER JOIN TABLEA A ON D.ENGID = CONVERT(VARCHAR,A.EN_ID) 
INNER JOIN TABLEB B ON B.ENGID = CONVERT(VARCHAR,A.EN_ID) 
INNER JOIN TABLEC C ON C.ENGID = CONVERT(VARCHAR,A.EN_ID)

check out the case keyword (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql) show us what you have so far as well. We're not mind readers :) — Jeremy, Mar 31 '17 at 19:43
Next time, start with the query. It will prevent answers which fail to capture the scope of your question or properly address it. Hypothetical questions are not [Minimal, Complete, or Verifiable examples](https://stackoverflow.com/help/mcve). To improve your answers, [review this thread](https://stackoverflow.com/help/how-to-ask) and include test data [as explained here](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) with expected results. Cheers — S3S, Mar 31 '17 at 20:04

score 0 · Answer 1 · answered Mar 31 '17 at 19:44

0

not sure what you mean by set testname to blank but if you meant to be using a SELECT query then you can do like

select *,
case when TESTNAME is null and serial_number = some_value then '' end as TESTNAME
from mytable

answered Mar 31 '17 at 19:44

Rahul

76,197
13
71
125

when B.TEST_NAME is null or B.TEST_NAME ='0' then '' instead of setting TEST_NAME blank, i want to set another column 'VALUES' which is in table D to BLANK since in table D i have a column(dropdownID) that is mapped to column(test_name) in table B. Hence , my objective is when TEST_NAME(in table b) is 0 or null, then set Values(in table D)to blank. I tried case when TEST.TEST_NAME IS NULL OR TEST.TEST_NAME = '0' then D.Values ='' but its says incorrect syntax. Please help me on this. Thank you. – Reddy Apr 03 '17 at 14:18
SELECT (A.PTNUMBER + '-' + A.SL_NO) AS ENUMBER, D.ENGINEER AS REQ, D.DATETIME as "DATE", case when SerialNo = xxx and B.TEST_NAME is null or B.TEST_NAME ='0' then '' else (select Value from DROPDOWN where B.TEST_NAME = CONVERT(VARCHAR, DropdownID)) end AS VALUES, TABLE_NAME AS TABLETD FROM INSPECTION D INNER JOIN TABLEA A ON A.ENGID = CONVERT(VARCHAR, A.EN_ID) INNER JOIN TABLEB B ON B.ENGID = CONVERT(VARCHAR, A.EN_ID) INNER JOIN TABLEC C ON C.ENGID = CONVERT(VARCHAR, A.EN_ID) INNER JOIN TABLED D ON D.ENGID = CONVERT(VARCHAR, A.EN_ID); – Reddy Apr 03 '17 at 14:18

score 0 · Answer 2 · edited May 23 '17 at 12:09

You could combine a case expression and coalesce() along with your join to choose the value you want to return.

select serial_number, ... 
  ,case when coalesce(testname,'') <> ''
          then t2.testname 
        else coalesce(testname,'') end
from t
  inner join t2
    on ...

You can use isnull() or coalesce() in sql server to return a different value to replace null.

select isnull(testname,'')

or

select coalesce(testname,'')

The main difference between the two is that coalesce() can support more than 2 parameters, and it selects the first one that is not null. More differences between the two are answered here.

select coalesce(testname,testname2,'')

coalesce() is also standard ANSI sql, so you will find it in most RDBMS. isnull() is specific to sql server.

Reference:

Below is my query, how can I set TESTNAME to blank if its NULL and perform join condition and populate values SELECT (A.PTNUMBER + '-' +A.SL_NO) AS ENUMBER, D.ENGINEER AS REQ, D.DATETIME as "DATE",(select Value from DROPDOWN where B.TEST_NAME=CONVERT(VARCHAR,DropdownID)) TESTNAME, TABLE_NAME AS TABLETD FROM INSPECTION D INNER JOIN TABLEA A ON D.ENGID = CONVERT(VARCHAR,A.EN_ID) INNER JOIN TABLEB B ON B.ENGID = CONVERT(VARCHAR,A.EN_ID) INNER JOIN TABLEC C ON C.ENGID = CONVERT(VARCHAR,A.EN_ID) — Reddy, Mar 31 '17 at 19:56

score 0 · Answer 3 · answered Mar 31 '17 at 20:03

0

SELECT (A.PTNUMBER + '-' + A.SL_NO) AS ENUMBER, 
       D.ENGINEER AS REQ, 
       D.DATETIME as "DATE",
       case
        when SerialNo = xxx and TESTNAME is null then ''
        else (select Value from DROPDOWN where B.TEST_NAME = CONVERT(VARCHAR, DropdownID))
       end AS TESTNAME, 
       TABLE_NAME AS TABLETD 
  FROM INSPECTION D
  INNER JOIN TABLEA A ON D.ENGID = CONVERT(VARCHAR, A.EN_ID) 
  INNER JOIN TABLEB B ON B.ENGID = CONVERT(VARCHAR, A.EN_ID) 
  INNER JOIN TABLEC C ON C.ENGID = CONVERT(VARCHAR, A.EN_ID);

answered Mar 31 '17 at 20:03

IngoB

2,552
1
20
35

I want to test if a TEST_NAME is null and 0 then set to blank but since TEST_NAME is a varchar, my SQL statement doesn't set null to blank, can you please help me with this? – Reddy Apr 03 '17 at 13:51
This is the condition TEST.TEST_NAME IS NULL OR TEST.TEST_NAME = '0' then '' – Reddy Apr 03 '17 at 13:51
I don't understand what you need, sorry. SELECT (A.PTNUMBER + '-' + A.SL_NO) AS ENUMBER, D.ENGINEER AS REQ, D.DATETIME as "DATE", case when SerialNo = xxx and TESTNAME is null then '' else (select cast(Value as varchar) from DROPDOWN where B.TEST_NAME = CONVERT(VARCHAR, DropdownID)) end AS TESTNAME, TABLE_NAME AS TABLETD FROM INSPECTION D INNER JOIN TABLEA A ON D.ENGID = CONVERT(VARCHAR, A.EN_ID) INNER JOIN TABLEB B ON B.ENGID = CONVERT(VARCHAR, A.EN_ID) INNER JOIN TABLEC C ON C.ENGID = CONVERT(VARCHAR, A.EN_ID); – IngoB Apr 03 '17 at 18:37

If else condition in MSSQL

3 Answers3