Thursday, August 8, 2013

Array in T-SQL

Array in T-SQL

Introduction 
Some junior developer ask me a question that "Can array be created in T-SQL".  This article is dedicated to them.
So the prime question is
Can we create the Array in T-SQL . My answer is NO.
How the problem be solved
As the T-SQL not provide any array
As we cannot create array in T-SQL, but we have alternate solution for that. We can create Table variable to solve the problem of array.
How to do that
Example of table variable creation
BEGIN
  DECLARE @myTabVar TABLE(monthno INT);
  INSERT INTO @myTabVar (monthno)
  VALUES (1), (2), (3);
  SELECT * FROM @myTabVar;
END
We can add the extra columns according to needs
BEGIN
  DECLARE @myTabVar TABLE(monthno INT, monthnm VARCHAR(50));
  INSERT INTO @myTabVar (monthno, monthnm)
  VALUES (1, 'Jan'), (2, 'Feb'), (3, 'Mar');
  SELECT monthno As [Month Number] , monthnm As [Month Name] FROM@myTabVar;
END
Output:
Month Number                                Month Name
1                                              Jan
2                                              Feb
3                                              Mar

We can use it as a normal table
UPDATE a
SET    a.monthsval = SUM(b.salval)
FROM   @myTabVar a
       INNER JOIN tbl_saleval b ON a.monthno = b.monthno;