CODEWITHSUNDEEP
sql-servert-sql
vabii
vabii

Reputation: 521

TSQL select into a variable

I am doing a simple 'select' into a variable but not able to understand the output of second snippet.

Working as expected:

declare @tname as varchar(100) = 'F99Special_DMT';

select top(1) @tname = form_name 
from [dbo].[forms] 
where form_name > @tname

print @tname; -- output = F99Special_Physical

forms table has rows ordered by form_name. F99Special_Physical is the value of last row.

declare @tname as varchar(100) = 'F99Special_Physical';

select top(1) @tname = form_name 
from [dbo].[forms] 
where form_name > @tname

print @tname; -- output = F99Special_Physical

Shouldn't it output null?

Thanks.

Upvotes: 1

Views: 3465

Answers (1)

Martin Smith
Martin Smith

Reputation: 452957

Nope. If the underlying SELECT returns zero rows (as will happen when using > the max value) no assignment is made and the initial value of the variable is retained.

You can use @@rowcount to check for this.

You need an order by to get deterministic results in the case that more than one row matches before the top.

DECLARE @tname AS VARCHAR(100) = 'F99Special_DMT';

SELECT TOP(1) @tname = form_name
FROM   [dbo].[forms]
WHERE  form_name > @tname
ORDER  BY form_name;

PRINT CASE
        WHEN @@ROWCOUNT = 1
          THEN @tname
      END;

Upvotes: 5

Related Questions