TSQL select into a variable

Question

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.

Martin Smith · Accepted Answer · 2015-08-15 12:09:12Z

5

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;

edited Aug 15, 2015 at 12:09

answered Aug 14, 2015 at 21:02

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TSQL select into a variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related