What does a SELECT statement without FROM used for?

Question

I am analyzing a stored procedure. There I came across a SELECT statement as below.

select @msg = 'spid: ' + convert(varchar(12), @@spid) + ' pre mat pull - PRODUCT_WORK COUNT = ' + convert(char(6), @@rowcount)

What does this statement without FROM do? How does it work? When do we use it?

Can't we use SET instead of SELECT?

Please help.

Answer 1

SELECT with no table will generate a result set projecting the columns and values that you've specified - this can be bound to a result set or reader in an application, in the same way that a table-bound SELECT works.

SELECT with no table is not an ANSI standard and will only work in certain RDBMS's (like SqlServer), but not all - e.g. Oracle's SELECT requires a table, but provides a pseudo table DUAL for this purpose.

It can also be used for assignment in SqlServer - SELECT will allow assignment of multiple variables in one statement:

SELECT @SomeVar = 1234, @AnotherVar = 'Foo';

Answer 2

In this particular case you can use set instead of select. But with select you can assign multiple variables at once, like

select @msg = 'msg', @data = 'data