DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to put the query results into a variable?

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

*/

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE