Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate assigning query results to local variables.

------------
-- Microsoft SQL Server T-SQL assign query result to variable
------------
USE AdventureWorks;
DECLARE  @FirstName VARCHAR(30),
         @LastName  VARCHAR(30)
SELECT @FirstName = c.FirstName,
       @LastName = c.LastName
FROM   HumanResources.Employee e
       INNER JOIN Person.Contact c
         ON e.ContactID = c.ContactID
WHERE  EmployeeID = 100
SELECT EmployeeFullName = @LastName + ', ' + @FirstName
PRINT @FirstName + ' ' + @LastName -- OUTPUT to Messages: Lane Sacksteder
GO
/* Results
 
EmployeeFullName
Sacksteder, Lane
*/
 
-- T-SQL literal query assignment to variables
DECLARE  @FirstName VARCHAR(30),
         @LastName  VARCHAR(30)
SELECT @FirstName = 'Marlon',
       @LastName = 'Brando'
SELECT Actor = @LastName + ', ' + @FirstName
PRINT @FirstName + ' ' + @LastName -- OUTPUT to Messages: Marlon Brando
GO
/* Results
 
Actor
Brando, Marlon
*/
 
-- SQL assignment of query results to numeric variable
DECLARE @POCount int
SELECT @POCount = COUNT(*)
FROM Purchasing.PurchaseOrderHeader
WHERE YEAR(OrderDate) = 2004
SELECT [Purchase Orders in 2004]=@POCount
GO
/*
Purchase Orders in 2004
2697
*/
 
-- SQL place query results into a variable using comma as delimiter
-- MSSQL convert rows into comma-limited string
DECLARE  @Departments VARCHAR(8000)
SET @Departments = ''
SELECT   @Departments = @Departments +  Name + ', '
FROM     AdventureWorks.HumanResources.Department
ORDER BY GroupName,
         Name
-- Remove last comma with LEFT string function
SELECT DepartmentsString=left(@Departments, LEN(@Departments)-1)
GO
/* Results
 
DepartmentsString
Executive, Facilities and Maintenance, Finance, Human Resources,
Information Services, Purchasing, Shipping and Receiving, Production,
Production Control, Document Control, Quality Assurance, Engineering,
Research and Development, Tool Design, Marketing, Sales
*/
 
-- MSSQL assign table count in database to variable - quotename function
-- T-SQL dynamic sql stored procedure with input / output parameters
CREATE PROCEDURE uspTableCount
                @DatabaseName SYSNAME,
                @Tables       INT  OUTPUT
AS
  BEGIN
    DECLARE  @DynamicSQL NVARCHAR(256), @Count INT
    SET @DynamicSQL = N'SELECT @Count = COUNT(*) FROM ' +
                              QUOTENAME(@DatabaseName) +
                     '.sys.tables'
    PRINT @DynamicSQL  -- Debug & test
    -- SELECT @Count = COUNT(*) FROM [AdventureWorks2008].sys.tables
    -- Dynamic SQL execution with output parameters
    EXEC sp_executesql
      @Query = @DynamicSQL ,
      @Params = N'@Count INT OUTPUT' ,
      @Count = @Count OUTPUT
    SET @Tables = @Count
  END
GO
 
-- Microsoft SQL Server T-SQL execute stored procedure
-- SQL Assign sproc result to variable
DECLARE  @AWtables INT
EXEC uspTableCount  'AdventureWorks2008' ,   @AWtables OUTPUT
SELECT 'AdventureWorks2008 table count' = @AWtables
GO
/* Results
 
AdventureWorks2008 table count
76

*/

------------