Today's question comes from David B.:

I think this might be a tough one. Basicly i do a join that result in a recordset. In one of the columns, i sometimes get a forign key, sometimes a NULL. What i want to do is to output some text+the forign key if there is a foreign key and nothing/NULL/some text if not so. One might think that IsNull is the answer but it's not since IsNull('sometext' + foreign_key, 'no fk') will never evaluate to NULL. In access97, there is this command call IIF (Immediate if) (IIF(expr, if_so, else)) that would let me do the trick (IIF(FK IS NULL, 'no fk', 'text' + fk)) but I can't find a corresponding function in SQL-server 6.5 or 7, that I use. I'd really appriciate some advice!

David,

David, here are a couple of solutions to this problem... (no IIF(), perhaps in SQL 7.5!)

1) Use a CASE expression:

SELECT foo, bar, CASE WHEN fk_col IS NULL THEN 'It is null!' ELSE 'the fk value is: ' + fk_col END FROM yadda

2) that fact that you mention "'sometext' + foreign_key will never evaluate to null" leads me to believe that you're using SQL 6.5. In SQL 7.0, the opposite is true... 'text'+NULL yields null, unless you use the SET CONCAT_NULL_YIELDS_NULL OFF command. If you start using SQL 7.0, you can use the (much simpler) ISNULL('text'+fk,'no fk')

There are probably other ways to do it (using some of the other NULL functions), but I think these would end up being the most readable.

Sean

Read Other SQL Guru Questions