Can a cast a nullable column to a NOT NULL column?

Question

In SQL 2005/8 I'm looking to convert a column in select query to a NOT NULL column.

coalease() and isnull(), although fine functions, is not what I'm looking for. I want the select to throw an error is any of the data is NULL.

Is this possible?

[Update] Clearly the best approach would be to modify the source table, unfortunatly in this case, it's not a (cost-effective) option.

Answer 1

If you want to throw an error in the (non-sql) code side, your kind-of only option is

$result = db.query("SELECT count(1) FROM table WHERE column_that_should_not_be_null IS NULL")
assert($result = 0);

If you want to transform the column to not accept NULL, when you do the ALTER TABLE ... SET NOT NULL, you'll get an error.

Answer 2

What you ask for doesn't exists.

NOT NULL is a constraint. SELECT statements do not enforce constraints. Constraints are only enforced during INSERT/UPDATE/DELETE of the data. If you need to enforce a constraint, declare the constraint on the table, via ALTER TABLE ... ADD...

If you want to return non-null data, use a null translation function like ISNULL or COALESCE.

Answer 3

An odd request, but I think this fulfils it:

DECLARE @TABLE TABLE
(
    ID INT IDENTITY(1,1) NOT NULL,
    Val VARCHAR(100) NULL
)

INSERT INTO @TABLE(Val)
SELECT 'Hello' 
UNION SELECT 'World' 
UNION SELECT NULL --Remove this line to prevent failure

SELECT ID, CASE WHEN Val IS NULL THEN CAST(1/0 AS VARCHAR) ELSE Val END AS ValCheck FROM @TABLE