Changing Case of Strings (Lower to Upper) in SQL Server

I have 2 databases, one with lowercase data and another with uppercase data.

DECLARE @NAME VARCHAR(40)
SELECT @NAME = UPPER(SE开发者_开发百科LECT NAME FROM DELETED)

By executing SELECT NAME FROM DELETED, I am selecting data that is lower case.

By executing SELECT @NAME = UPPER(SELECT NAME FROM DELETED), I would want to select uppercase data on the query inside the UPPER().

Question is can I use the UPPER() with the SELECT, like the query above?

How about

SELECT UPPER(NAME) FROM DELETED

instead of

UPPER(SELECT NAME FROM DELETED)

You'll need an extra pair of brackets

Select @NAME = UPPER((SELECT NAME FROM DELETED));

(Not that I'd do it that way, see Lukas's answer for a better approach).

Use

SELECT @pNAME = UPPER([NAME]) FROM DELETED