I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to alter/delete any of the already created tables in the AdventureWorks or TSQL2012 databases. I've created a temp table to test my code before I actually create a table to play with and this is the code I am using to insert values into my table:
DECLARE @i INT = 1 WHILE @i < 10 BEGIN INSERT INTO #TestEmployeeCountry VALUES ( SUBSTRING('ABCDEFGHIJKLMNOP', @i, 1), CASE (SELECT ABS(CHECKSUM(NEWID()))%10 +1) WHEN 1 THEN 'USA' WHEN 2 THEN 'CANADA' WHEN 3 THEN 'MEXICO' WHEN 4 THEN 'UK' WHEN 5 THEN 'FRANCE' WHEN 6 THEN 'SPAIN' WHEN 7 THEN 'RUSSIA' WHEN 8 THEN 'CHINA' WHEN 9 THEN 'JAPAN' WHEN 10 THEN 'INDIA' END) SET @i = @i + 1 END;
The problem I am having is I keep getting an error saying "Cannot insert the value NULL into column 'Country', table 'tempdb.dbo.#TestEmployeeCountry" The reason this is is because I have the Country column set to NOT NULL, and my code does work for some of the inserts, the problem is I randomly get NULL values out of my case statement.
I know that to fix this I can easily add another line that says "DEFAULT xxxxxx" however I want to understand what is going on because based on what I see I shouldn't have to do that, should I? I thought I wrote my case statement correctly, giving me a number between 1-10 only and upon testing just that specific select statement over 1000 tries, I always get a random number between 1-10, nothing larger or smaller. Can anyone help me to understand why this code tries to enter a NULL value into that column?