Wednesday, November 21, 2012

SQL SERVER – Storing Variable Values in Temporary Array or Temporary List

SQL SERVER – Storing Variable Values in Temporary Array or Temporary List

Let us say here are the values: a, 10, 20, c, 30, d. Now the requirement is to store them in a array or list. It is very easy to do the same in C# or C. However, there is no quick way to do the same in SQL Server. Every single time when I get such requirement, I create a table variable and store the values in the table variables. Here is the example: For SQL Server 2012:
DECLARE @ListofIDs TABLE(IDs VARCHAR(100));
INSERT INTO @ListofIDs
VALUES('a'),('10'),('20'),('c'),('30'),('d');
SELECT IDs FROM @ListofIDs;
GO

When executed above script it will give following resultset. Above script will work in SQL Server 2012 only for SQL Server 2008 and earlier version run following code.
DECLARE @ListofIDs TABLE(IDs VARCHAR(100), ID INT IDENTITY(1,1));
INSERT INTO @ListofIDs
SELECT 'a'
UNION ALL
SELECT '10'
UNION ALL
SELECT '20'
UNION ALL
SELECT 'c'
UNION ALL
SELECT '30'
UNION ALL
SELECT 'd';
SELECT IDs FROM @ListofIDs;
GO
Now in this case, I have to convert numbers to varchars because I have to store mix datatypes in a single column. Additionally, this quick solution does not give any features of arrays (like inserting values in between as well accessing values using array index).

If you are searching life partner. your searching end with kpmarriage.com. now kpmarriage.com offer free matrimonial website which offer free message, free chat, free view contact information. so register here : kpmarriage.com- Free matrimonial website