Set variable to NULL if not found in query statement

Question

I have following code

DECLARE @a INT

SET @a = 1

SELECT TOP 1
    @a = id
FROM
    test
WHERE
    name = 'notexist'

SELECT @a

variable @a will still have 1 value if the SELECT statement doesn't find any row, is it possible to set the @a variable to null if the SELECT statement doesn't find any row without adding SET @a = NULL before SELECT statement?

Answer 1

Try this

SELECT TOP 1
    @a = id
FROM
    test
WHERE
    name = 'notexist'

If @@rowcount = 0
set @a = NULL

or

set @a = case when @@rowcount = 0 then null else @a end

But its better to avoid the initialization of 1. By default it will be NULL

Answer 2

Assign value this way

SET @a = (
SELECT TOP 1
    id
FROM
    test
WHERE
    name = 'notexist'
)

SELECT @a