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;

Hope you like it.

Posted by: MR. JOYDEEP DAS