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;
