Problem in reading the database collation into a variable in SQL Server

Question

I've faced this strange thing that I can't set the value of a variable to a result of a query. The query is reading the collation of a database and works fine by itself:

SELECT collation_name 
FROM sys.databases
WHERE [name] = 'my_DB';

And the result is like: SQL_Latin1_General_CP1_CI_AS

But when I want to put the result in a variable, I get no result (and no error)

declare @DB_collation varchar(100);
set @DB_collation = (SELECT 
    collation_name 
    FROM sys.databases
    WHERE name = 'my_DB')  
print @DB_collation;

I have no problem in setting the result of other SELECT queries into a varchar variable.

Answer 1

This is the way to do it:

declare @DB_collation varchar(100);
SELECT  @DB_collation = 
    collation_name 
    FROM sys.databases
    WHERE name = 'tempdb' 
    
print @DB_collation;

Put your variable after the select keyword.

Here is a demo:

DEMO

If this does not work then you can select the value of the variable:

declare @DB_collation varchar(100);
SELECT  @DB_collation = 
    collation_name 
    FROM sys.databases
    WHERE name = 'tempdb' 
    
select @DB_collation;

P.S: Please look your results of the PRINT statement in Messages tab and not in Results tab