Boyan Kostadinov just sent me a cool link to an article that is the final part in a four part series that discusses the SQL NULL value.
Alternatively, you are looking for a method to validate a number based on a check digit: you could be using this as part of a password reset check, for account verification or similar purposes.
I find that in most cases, having a zero is graphically equivalent to NULL and a whole lot easier to deal with. I've had this problem in strange statistics data for a long time and its always been a little bit tricky to over come, this is a nice solution, and its all done in SQL too. Wish I would have known about this a long time ago - I've always just used a case statement:case when is Null(divisor, 0) = 0 then 0 else numerator/divisor end as value But this seems much nicer! VERY late to the party here, but I had occasion to work on a Divide By Zero error today, and came across your post. good article...colsec() method has better result than Isnull().in place of Isnull() method use Colsec()is more standard and recommended by Microsoft Sql Server ....
In the past I've pulled the math out of SQL and had CF do it as I had more control over the devision by zero, this makes a hell of a lot more sense. With MSSQL (2005 anyway) you can add these two lines ahead of the query that could potentially fail with a DBZ error: SET ARITHABORT OFFSET ANSI_WARNINGS OFFWith both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error. Thanking you--Mohammad Shahnawaz India @Mohammad, I guess you meant COALESCE() which is mentioned in the article.
DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql = N' ALTER TABLE ' QUOTENAME(s.name) '.' QUOTENAME(t.name) ' ALTER COLUMN ' QUOTENAME(c.name) ' ADD PERSISTED;' FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.[object_id] = t.[object_id] INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE COLUMNPROPERTY(t.[object_id], c.name, N'Is Indexable') = 1 AND COLUMNPROPERTY(t.[object_id], c.name, N'Is Deterministic') = 1 AND t.uses_ansi_nulls = 1 AND c.is_computed = 1 AND EXISTS ( SELECT 1 FROM sys.computed_columns WHERE [object_id] = t.[object_id] AND column_id = c.column_id AND is_persisted = 0 ); PRINT @sql; --EXEC sp_executesql @sql; command, but that is not because the variable is somehow truncated.
This still should allow you to validate that the code outputs the DDL commands you roughly expect, before blindly executing them.