There are a few ways to do exactly what you want to do, and I will show you at the end of this post. But what you want to do is probably not what you really want to do. 😉

You wrote that you wanted to "simplify" the code by using a variable. Does this mean you intend to use a cursor or a while loop as well? After all, in the first query you are performing a set based operation: "take every value in the cdsDescription column of a table, and apply an expression to it". But in the "simplification" you can only ever be working with one particular cdsDescription, since @FirstBlankPosition is a scalar variable, and a scalar variable cannot contain more than one value. So if you want the "simplified" code to do the same thing as the original code, you would have to run it many times (as many times as there are rows in the tbCompdistances table).

In other words, your first code example is ultimately simpler... and much faster as well.

The only time you might want to use the "variable" version (although I still wouldn't) is if you are absolutely certain that you will only ever be working with a single row. Maybe the tbCompDistances table can only ever contain one row (but my intuition is that this is unlikely given the name of the table).

Anyway, as promised, here's some code for you:

declare @t table (i int)

insert @t select 1 union all select 2

declare @i int

-- this won't work, since "set" expects a scalar value

set @i = (select i from @t)

-- this will "work" (in that it will execute without error).

-- but the @i variable can still only contain a single value. Will the value be 1 or 2? How do you know?

select @i = i from @t

-- this will work. The "top 1" means the engine knows that only a single row will be returned

-- plus, we have told it what we want to order by, so we know that the @i variable will be 1 after this execution

-- But as I said above, you PROBABLY DON'T WANT TO DO THIS.

set @i = (select top 1 i from @t order by i)