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.

Solution 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';

Solution 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

Solution 3

You must understand the @ and @@

@msg = local variable 

@@spid and  @@rowcount = global variable

the set and select is almost same but the select can get value from DB. and Set cannot get value from DB.

Comments

[major-mann]

One more with this, is that you can use it to union variables into result sets. ie... select <vars> union all select <columns> from <table>