Re: Question on Structuring Product Attributes

Op vrijdag 8 februari 2013 23:30:46 UTC+1 schreef derek.a..._at_gmail.com het volgende:

(snipped most)

> I generalised that [tutorial] code to work with most SQLs (eg. COALESCE() is SQL but Sybase and others had ISNULL() to do the same thing. The syntax and operation of ISNULL is intuitive; COALESCE is non-intuitive.). If we don't have a row at all, the SELECT returns NULL. The COALESCE converts the NULL to zero, in order to RETURN an INT.

1. You don't have to convert NULL to zero in order to return an int. Any data type in a relational database should support a NULL value that is distinct from any other value allowed by the data type, so you can return NULL as an int.
2. The SELECT actually returns an empty set, not a NULL. It is converted to NULL as part of the implicit cast required in order to RETURN an int value.
3. In order to get the COALESCE to do what you claim it does, you need to change the order: RETURN COALESCE((SELECT ...), 0). This will convert the empty set to NULL, then replace it with zero.

Proof:
SELECT dbo.ValidateExclusive_fn(1234, 'B') returns NULL

If it returns zero on other database platforms, it is either a bug or a deviation from the ANSI standard.

I think the code in the function can be simplified to omit the COALESCE, and the CHECK constraints changed to CHECK (dbo.ValidateExclusive_fn (ProductId, 'B') IS NOT NULL)

Cheers,
Hugo Received on Sat Feb 09 2013 - 02:13:12 CET