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 from Pரதீப் on Stack Overflow
🌐
GeeksforGeeks
geeksforgeeks.org › sql › how-to-set-a-column-value-to-null-in-sql
How to Set a Column Value to Null in SQL? - GeeksforGeeks
In this article 'How to Set a Column Value to Null in SQL', we have reached to some of the basic conclusions, that are listed below. Use the table name in the UPDATE statement. Put NULL in the column name. To select which rows to update, use ...
Published: July 23, 2025
🌐
W3Schools
w3schools.com › sql › sql_null_values.asp
SQL NULL Values - IS NULL and IS NOT NULL
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL; Try it Yourself » · Tip: Always use IS NULL to look for NULL values. The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
🌐
Scaler
scaler.com › home › topics › sql › null value in sql
NULL Value in SQL - Scaler Topics
March 28, 2024 - The above query will check the ... his contact number. So please mark the Contact Number of Vaibhav as NULL. In the above scenario, we can use the assignment operator = to set Vaibhav's contact number as NULL....
🌐
W3Schools
w3schools.com › sql › sql_isnull.asp
SQL COALESCE(), IFNULL(), ISNULL(), NVL() Functions
COALESCE() - The preferred standard. (Works in MySQL, SQL Server and Oracle) ... Note: A NULL value represents an unknown or missing data in a database field.
Top answer
1 of 2
3

When the where clause of the select filter out every records then no assignation is made. Therefore, @x has the value of the last time it was assigned.

Try this way instead :

SELECT @X = (SELECT ContractDate
             FROM dbo.Foo C
             WHERE ProjectCode = 130192 
             AND VersionID = (SELECT MIN(VersionID) 
                              FROM dbo.Foo 
                              WHERE ProjectCode = C.ProjectCode))

This way, your assignation "select" doesn't contains any where clause, thus it will always be executed. If the inner select doesn't result anything, then null is assigned to X.

Here is another example

DECLARE @x NVARCHAR(25) = 'Initial value'

SELECT @x = 'Value1'
WHERE 0 = 1

--@x is still "Initial value" cause 0 isn't egal to 1

SELECT @x = (SELECT 'Value2'
             WHERE 0 = 1)

--@x is now null because the impossible where clause didn't hindered the assignation. It has just made the inner select to return null instead.

As Martin suggestion, you can instead reset the value before with

SELECT @X = null

but I prefer to have just one assignation select because it's shorter, and it is one less instruction line for the SQL Engine.


Side note :

Your "select" could be rewritten with an "Order by" and "TOP 1" clause to get the minimum. It's give the same result and is shorter to be write/read.

SELECT @ProjectContractDate_T0 = (SELECT TOP 1 ContractDate
                                  FROM dbo.FooFoo C
                                  WHERE ProjectCode = @ProjectCode 
                                  ORDER BY VersionID)

It does the same thing.

2 of 2
3

When setting a variable in a select statement, if that select statement returns no rows, then there will be no assignment; your variable's value will not change.

As to your loop problem, the scope of a variable in T-SQL is the batch, not the loop. It is not re-declared or initialized with each iteration.

As such, it can be a good practice in T-SQL to explicitly initialize your variables to null when you otherwise might expect a re-initialization in a different language. This might make it more readable and better show your intent.

Also, I should add that cursors and loops in T-SQL are usually best avoided when a set-based solution (query) is possible, which is almost always. Obviously this depends on your use case, which wasn't stated.

Find elsewhere
🌐
TutorialsPoint
tutorialspoint.com › article › how-to-set-a-column-value-to-null-in-sql
How to Set a Column Value to Null in SQL?
October 11, 2024 - INTO Customers VALUES (1, 'Nidhi', ... example sets the Gender of the female customers to "NULL" ? UPDATE Customers SET Gender = NULL WHERE Gender = 'F'; SELECT * FROM Customers;...
🌐
TutorialsTeacher
tutorialsteacher.com › sql › sql-null-value
SQL - NULL Value
To fetch the NULL values from a table, we can use keywords, NULL or NOT NULL. You can not select NULL data of a table by using any comparison operators (e.g. =, !=, >, < ).
🌐
SQLServerCentral
sqlservercentral.com › home › topics › assigning null value to the variable
Assigning Null value to the variable – SQLServerCentral Forums
August 25, 2008 - Setting a variable to null is as simple as SET @VariableName = NULL · select @col1 = isnull(col1,NULL),@col2 = isnull(col2,null) from table · What are you trying to acheive here? ISNull is a function that returns the first value if the first value is not null and the second value if it is null.
🌐
GeeksforGeeks
geeksforgeeks.org › sql › sql-null-values
NULL values in SQL - GeeksforGeeks
January 5, 2026 - To do so, we can use the IS NULL operator in the WHERE clause to select the rows with NULL values and then we can set the new value using the SET keyword. Let's suppose that we want to update SSN in the row where it is NULL.
🌐
Medium
crmprogrammer.medium.com › 5-different-ways-to-work-with-nulls-in-sql-server-queries-90005df8de04
5 different ways to work with nulls in SQL Server queries | by Harshad - CRM Programmer | Medium
August 26, 2021 - ... 1. Set a default value using ISNULL(): When a record has a null value for a field, a default value can be set to this null. Following simple query gives an idea about the syntax of the default statement in a select ...
🌐
SQL Easy
sql-easy.com › learn › how-to-use-null-values-in-sql
How to Use Null Values in SQL? A Comprehensive Guide for Beginners | SQL Easy
September 23, 2023 - We could use COALESCE like so: SELECT COALESCE(FirstName, LastName) AS CustomerName FROM Customers; That way, if FirstName is NULL, it’ll show the LastName instead. Thirdly, there’s also the CASE statement which gives us more control over ...
🌐
ProjectPro
projectpro.io › recipes › null-values-sql
What are SQL NULL Values? - All That You Need to Know! -
January 11, 2024 - SQL Project for Data Analysis using ... records where a specific column has non-NULL values. For instance: SELECT * FROM Orders WHERE ShippedDate IS NOT NULL;...
🌐
SQL Shack
sqlshack.com › working-with-sql-null-values
Working with SQL NULL values
May 19, 2021 - However, we can disable this behavior pattern by using the SET ANSI_NULLS command. By default, this option status is ON but we can disable this option during the session-level so the equals (=) and not equal (<>) comparison operators do not ...