Exclude Records with Null Values

[foxbldr]

Hi All,

I have database with about 200 tables and each table has field called 'Rec_status'. When Rec_status is null it is considered as an active record. In order to extract active records from tables, I have to add additional conditions with WHERE clause. Example:

Select a.*, b.*, c.* from tbl1 a, tbl2 b, tbl3 c where ....... and not is null a.rec_status and not is null b.rec_status and not is null c.rec_status

Can somebody advise me how to do the same thing without writing conditions to check Rec_status of every table with every sql statement? In other words, is there way to tell SQL Sever not to include such records whenever a sql stament is executed?

Thank you all in advance.
foxbldr

 

[SQLSister]

YOu would need this every time, yes. However, you could set up views for each table that exclude records where the field is not null and then use the views to create your queries and stored procedures. At least this way, you only have to write the code to exclude the non active records once.

 

[foxbldr]

SqlSister,

Thanks for the reply.
What you suggested should work but my real problem is changing existing SQL statments.Hundreds of SQL statement have been written with client application without checking this status.

I thought that I should do something at the server side without changing existing sql statements.


I am still waiting for a better suggestion.

Thanks once again.

Foxbldr