DECLARE @AddressLine1 nvarchar(60) = 'Heldenplatz';

SELECT AddressId, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';

DECLARE @AddressLine1 nvarchar(60);
SET @AddressLine1 = 'Heldenplatz';

SELECT AddressId, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';

DECLARE @AddressLine1 nvarchar(60);
DECLARE @AddressLine2 nvarchar(60);

SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;

SELECT @AddressLine1 AS 'Address1', @AddressLine2 AS 'Address2';

DECLARE @AddressLine1 nvarchar(60) = '59. Baker Street';
DECLARE @AddressLine2 nvarchar(60) = 'Reception Desk';

SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;

IF @@ROWCOUNT = 1
   SELECT @AddressLine1 AS 'Address1', @AddressLine2 AS 'Address2'
ELSE
   SELECT 'Either no rows or to many rows found';

DECLARE @QuerySelector int = 3;

IF @QuerySelector = 1 BEGIN
   SELECT TOP 3   ProductId, Name, COlor
   FROM Production.Product
   WHERE Color = 'Silver'
   ORDER BY Name
END 
ELSE BEGIN
   SELECT TOP 3   ProductId, Name, Color
   FROM Production.Product
   WHERE Color = 'Black'
   ORDER BY Name
END;

SELECT DepartmentID AS DeptID, Name, GroupName, 
   CASE GroupName -- map group name column
      WHEN 'Research and Development' THEN 'Room A'
      WHEN 'Sales and Marketing' THEN 'Room B'
      WHEN 'Manufacturing' THEN 'Room C'
      ELSE 'ROOM D'
   END AS ConferenceRoom -- into new ConferenceRoom column
FROM HumanResources.Department

SELECT DepartmentID AS DeptID, Name
   CASE 
      WHEN Name = 'Research and Development' THEN 'Room A'
      WHEN (Name = 'Sales and Marketing' OR DepartmentID = 10) THEN 'Room B'
      WHEN Name LIKE '%T' THEN 'Room C'
      ELSE 'ROOM D'
   END AS ConferenceRoom -- into new ConferenceRoom column
FROM HumanResources.Department

-- Declare variables 
DECLARE @AWTables TABLE (SchemaTable varchar(100)); 
DECLARE @TableName varchar(100); 

-- Insert table names into the table variable 
INSERT @AWTables (SchemaTable) 
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME  
   FROM INFORMATION_SCHEMA.tables  
   WHERE TABLE_TYPE = 'BASE TABLE'  
   ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME;

-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0 BEGIN
   -- select first entry from the AWTables list
   SELECT TOP 1   @TableName = SchemaTable 
   FROM @AWTables
   ORDER BY SchemaTable;
   
   -- execute stored procedure to determine table size 
   EXEC sp_spaceused @TableName; 
   
   -- remove entry from the AWTables list
   DELETE @AWTables 
   WHERE SchemaTable = @TableName;
END;