G’day,
SQL Server’s great – its been teaching me a lot for many years. But every so often you get a surprise. Sometimes it’s a nice one, sometimes it more of the ‘really, are you sure kind’ – and then you realise, yes SQL Server – you’re correct, I really should have known that.
This one involved
- A cursor
- String concatenation
- Executing a T-SQL Statement
The first two of these didn’t suprise me – after I’d thought about it.
The last one did
I’ll attempt to give an example of what occured
I have a table
USE tempdb;
GO
IF OBJECT_ID('dbo.Issue') IS NOT NULL
BEGIN
DROP TABLE dbo.Issue
END;
CREATE TABLE dbo.Issue
(
SomeName NVARCHAR(50) NULL
);
and, we’re inserting into that table using a concatenated T-SQL string, like so
DECLARE @InsertValue NVARCHAR(50) = N'TestValue';
DECLARE @SQL NVARCHAR(500) = 'INSERT INTO dbo.Issue(SomeName) VALUES(''' + @InsertValue + ''');';
PRINT @SQL;
EXEC(@SQL);
As you’d expect one row goes in
However, the issue that I saw arose when the variable being INSERTed was NULL, like so
DECLARE @InsertValue NVARCHAR(50) = NULL;
DECLARE @SQL NVARCHAR(500) = 'INSERT INTO dbo.Issue(SomeName) VALUES(''' + @InsertValue + ''');';
PRINT @SQL;
EXEC(@SQL);
You’ll notice (if you run the code) that nothing gets printed – which is fair enough as a NULL in a string concatenation will unltimatly yeild a NULL, as anything + an unknown (NULL) is an unknown (NULL)
That wasn’t the surprising part – although it did get me to think a bit at first.
The surprising part (to me at least), was that the string – which is now NULL -has been run inside the EXEC(..) call, had not errored, had not failed, but had not inserted anything either
Try it
DECLARE @InsertValue NVARCHAR(50) = NULL;
DECLARE @SQL NVARCHAR(500) = 'INSERT INTO dbo.Issue(SomeName) VALUES(''' + @InsertValue + ''');';
PRINT @SQL;
EXEC(@SQL);
SELECT * FROM dbo.Issue;
You can try this more simply by executing a NULL string
DECLARE @Test NVARCHAR(20) = NULL;
EXEC(@Test);
Note however, that you can’t directly execute a NULL (although why you’d want to is a different matter 🙂 )
EXEC(NULL)
Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'NULL'.
So, now you know.
If you’ve never seen this before (or even if you have, but a long time ago) then this could easily make you wonder what is going on.
I hope this helps somebody.
Have a great day.
Cheers
Martin.