When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
The SQL Guru Answers your Questions...

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, here are a couple of solutions to this problem... (no IIF(), perhaps in SQL 7.5!)

1) Use a CASE expression:

	WHEN fk_col IS NULL THEN 'It is null!'
	ELSE 'the fk value is: ' + fk_col
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.


Read Other SQL Guru Questions

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article