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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
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,

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


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