I have a Guid table type who I declare as:
@MyTableVariable [HELPER].[GuidIdTableType] READONLY
For testing propouses I add an empty guid like:
INSERT INTO @MyTableVariable (Id) VALUES ('00000000-0000-0000-0000-000000000000')
So I have a BIT variable who I want to return null if is an empty guid or table is null:
@MyTableVariable [HELPER].[GuidIdTableType] READONLY
INSERT INTO @MyTableVariable (Id) VALUES ('00000000-0000-0000-0000-000000000000')
DECLARE @IsAllSelected BIT = (SELECT TOP 1
1
FROM @MyTableVariable
WHERE ([Id] = '00000000-0000-0000-0000-000000000000'
OR [Id] IS NULL))
SELECT @IsAllSelected
In this case it return 1 because it has empty guid, so that's correct, but if I remove empty guid from script as:
@MyTableVariable [HELPER].[GuidIdTableType] READONLY
DECLARE @IsAllSelected BIT = (SELECT TOP 1
1
FROM @MyTableVariable
WHERE ([Id] = '00000000-0000-0000-0000-000000000000'
OR [Id] IS NULL))
SELECT @IsAllSelected
It returns null, WHERE clause OR [Id] IS NULL no work, it's supposed it shoudl returns 1 too because is empty. What am I doing wrong?
As Joren indicated, when recordset returns no rows you will get NULL value assigned to the variable.
The way you write this is:
DECLARE @IsAllSelected BIT = ISNULL((SELECT TOP 1 1 FROM @MyTableVariable
WHERE ([Id] = '00000000-0000-0000-0000-000000000000' OR [Id] IS NULL)), 0 )
The variable will be set to 1 when record is found or 0 when record is not found.
Some notes on TOP 1:
TOP 1 ensures that expression will not throw an error if two (or more) rows are returned. Otherwise it is useless in this case.
Another way to do it is using COUNT(*) as this will always return a value:
DECLARE @IsAllSelected BIT = (SELECT COUNT( * ) FROM @MyTableVariable
WHERE ([Id] = '00000000-0000-0000-0000-000000000000' OR [Id] IS NULL))