One of the ways to set the value of a variable is with a SELECT statement. This has the benefit of being able to set multiple variables at once.

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0
SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE name = 'master'
SELECT @create_date AS Create_Date, @database_id AS Database_Id

This is a very useful technique but you do have to be careful. What happens can vary depending on the number of rows you get back from the query.

One row

This is the best case and will act exactly like you expect. The value for each variable will be the specified values from the row returned.

Multiple rows

If you have multiple rows being returned then the values stored in the variables will be from the <em>last</em> row returned from the query.  

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0
SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
SELECT @create_date AS Create_Date, @database_id AS Database_Id

The problem here is that last is pretty uncertain unless you put an ORDER BY into the query. Remember that without an ORDER BY, a query has no guaranteed order. It might be in the same order hundreds of times and then change for some reason (a new index was added, SQL was updated, or something even less obvious).

No rows

This one can get really confusing if you aren't aware it can happen. If your query returns no rows then the variables will remain unchanged.

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0
SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE 1=0
SELECT @create_date AS Create_Date, @database_id AS Database_Id

You might think that you could change the query to use ISNULL to avoid the problem:

SELECT @create_date = ISNULL(create_date,'12/31/2099'), 
@database_id = ISNULL(database_id,-1)
FROM sys.databases
WHERE 1=0

But this doesn't help. The problem is that without any rows being returned no update will happen. Putting an ISNULL around the result (or any other functions you try to use) will only make a difference if a row is actually being returned. There best way to avoid this particular problem is to initializing your variables ahead of time. Also if you are resetting the variable multiple times make sure that you reinitialize it within each code block. For example:

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0
SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE name = 'master'
/* Code goes here */SET @create_date = '1/1/1900'
SET @database_id = 0
SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE name = 'SSISDB'
/* Code goes here */

In the code above I'm pulling the information for master, then I'm doing some processing on that information. Next, I'm pulling information about SSISDB and processing it. If I don't reinitialize the variables and don't happen to have an SSISDB then I'm going to reprocess master.

Using SELECT to store values into a variable is something you will have to know how to do if you are doing much coding in T-SQL. But you also have to know how it reacts and how to avoid problems.