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.
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:
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