I was reminded of a little quirk of SQL recently, and I thought I would share it with my readers. I will be going over the SQL in a manner suitable for beginners, but the quirk that I refer to could catch an intermediate student of SQL. I will use some simple SQL to illustrate my point.
In step 1, we create an empty table. In step 2, we declare two variables, and their initial value is NULL. Step 3 should assign a value to both variables, BUT since there are no records, @cnt receives a value of ZERO, while @sum is set to NULL. This is counter-intuitive for me, but "it is what it is". Since the statement "SELECT @variable=value FROM thetable;" doesn't assign a value consistently, I like to add a statement like "SET @variable = INIT_VALUE;" or "SELECT @cnt = 0, @sum = 0 ;" first. As an exercise, try min and max statements. Try inserting 1 record between steps 2 and 3, and doing a SELECT ... WHERE id = -1; (a wrong value).
By the way, you will notice that I add a space, then a semicolon at the end of each statement. I do this for two reasons: 1) it helps me to catch mistakes which might otherwise be hidden; 2) a former co-worker had minor vision problems and told me that it was easier to see the semicolon if it was separated from the last word by one space (Hi, Carolyn!).
If you will paste this sequence into query analyzer, and run it in a non-production database, you will see that it produces:
-- 1. create temporary table
CREATE TABLE #test1
(
id int not null ,
name varchar(50) not null
) ;
-- 2. declare variables
DECLARE @cnt int, @sum int ;
-- 3. compute statistics
SELECT @cnt = count(*) FROM #test1 ;
SELECT @sum = sum(id) FROM #test1 ;
-- 4. print results to query analyzer
PRINT '@cnt = ' + ISNULL( CONVERT(varchar, @cnt), '[NULL]') ;
PRINT '@sum = ' + ISNULL( CONVERT(varchar, @sum), '[NULL]') ;
-- 5. cleanup
DROP TABLE #test1 ;
@cnt = 0
@sum = [NULL]
In step 1, we create an empty table. In step 2, we declare two variables, and their initial value is NULL. Step 3 should assign a value to both variables, BUT since there are no records, @cnt receives a value of ZERO, while @sum is set to NULL. This is counter-intuitive for me, but "it is what it is". Since the statement "SELECT @variable=value FROM thetable;" doesn't assign a value consistently, I like to add a statement like "SET @variable = INIT_VALUE;" or "SELECT @cnt = 0, @sum = 0 ;" first. As an exercise, try min and max statements. Try inserting 1 record between steps 2 and 3, and doing a SELECT ... WHERE id = -1; (a wrong value).
By the way, you will notice that I add a space, then a semicolon at the end of each statement. I do this for two reasons: 1) it helps me to catch mistakes which might otherwise be hidden; 2) a former co-worker had minor vision problems and told me that it was easier to see the semicolon if it was separated from the last word by one space (Hi, Carolyn!).