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

SQL Server does not support arrays or a dynamic length storage mechanism like list. Absolutely there are some clever workarounds and few extra-ordinary solutions but everybody can;t come up with such solution. Additionally, sometime the requirements are very simple that doing extraordinary coding is not required. Here is the simple case.

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.

SQL SERVER - Storing Variable Values in Temporary Array or Temporary List arraykind

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).

Well, do you ever have to store temporary multiple values in SQL Server – if the count of values are dynamic and datatype is not specified early how will you about storing values which can be used later in the programming.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Last Two Days to Get FREE Book – Joes 2 Pros Certification 70-433
Next Post
SQL SERVER – Move Database Files MDF and LDF to Another Location

Related Posts

No results found.

35 Comments. Leave new

  • Sandeep Mittal
    October 27, 2012 9:58 am

    Dear Pinal

    The above script will even work in SQL Server 2008 also as both table variable and grouping sets are available in SQL Server 2008 onwards

    Reply
  • Hi PInal,

    Given query I tried on “Sql Server 2008 R2” as well as “Sql Server 2012” it’s working in both of them.
    ——————————————————————–
    DECLARE @ListofIDs TABLE(IDs VARCHAR(100));
    INSERT INTO @ListofIDs
    VALUES(‘a’),(’10’),(’20’),(‘c’),(’30’),(‘d’);
    SELECT IDs FROM @ListofIDs;
    ——————————————————————–

    In “Sql Server 2005” it’ll not work.

    Reply
  • Nice one…………………

    Reply
  • Nice one

    Reply
  • For multi datatype,one can use sql_variant datatype supported since 2000.
    Though I have never use this.I read it has some limitation.
    https://www.microsoft.com/en-us/download/details.aspx?id=51958

    So when should i choose sql_variant over varchar ?what is the advantage of sql_variant ?

    Reply
  • If for multidatatype varchar is use then to manipulate those data ,one has to use isnumeric,isdate etc. and do manipulation,which is little lengthy process.Beside this one cannot find the exact other datatype beside these two isdate and isnumeric.So process is lengthy and complicated.

    In case of Sql_variant,same thing is very easy.We can use sql_variant_property to do so.

    declare @t table(col1 sql_variant )
    insert into @t values(1),(‘2’)
    Select sql_variant_property(col1, ‘BaseType’) from @t

    vs

    declare @t table(col1 varchar(100) )
    insert into @t values(1),(‘2’)
    Select sql_variant_property(col1, ‘BaseType’) from @t

    Reply
  • Girijesh Pandey
    October 30, 2012 6:35 pm

    Yes, it works only with SQL Server 2008 onwards.

    Reply
  • Hi Pinal ,
    If values are stored in variable then how above script.works.
    e.g:
    Declare @var NVARCHAR(max)
    @var = ‘a,b,c,d’

    Reply
  • We can also get a similar result using CTE

    Declare @stringvar VARCHAR(max)
    set @stringvar = ‘a,10,20,c,30,d’;
    with cte(IDs,stringvalue,id)
    as
    (
    select cast(” as VARCHAR(MAX)), @stringvar, 0
    union all
    select left(stringvalue,charindex(‘,’,stringvalue,0)-1), right(stringvalue,len(stringvalue)-charindex(‘,’,stringvalue,0)), id+1 from cte where charindex(‘,’,stringvalue,0)>0
    union all
    select stringvalue,”, id+1 from cte where charindex(‘,’,stringvalue,0)=0 and stringvalue”
    )
    select IDs from cte where IDs”

    Reply
  • DECLARE @ListofIDs TABLE(IDs VARCHAR(100));
    INSERT INTO @ListofIDs
    VALUES(‘a’),(’10’),(’20’),(‘c’),(’30’),(‘d’);
    SELECT IDs FROM @ListofIDs;
    script is also works in Sql Server 2008 R2.
    Check this out..

    Reply
    • Kiran subramanian
      June 4, 2015 4:53 pm

      DECLARE
      type namesarray IS VARRAY(5) OF VARCHAR2(10);
      type grades IS VARRAY(5) OF INTEGER;
      names namesarray;
      marks grades;
      total integer;
      BEGIN
      names := namesarray(‘Kavita’, ‘Pritam’, ‘Ayan’, ‘Rishav’, ‘Aziz’);
      marks:= grades(98, 97, 78, 87, 92);
      total := names.count;
      dbms_output.put_line(‘Total ‘|| total || ‘ Students’);
      FOR i in 1 .. total LOOP
      dbms_output.put_line(‘Student: ‘ || names(i) || ‘
      Marks: ‘ || marks(i));
      END LOOP;
      END;
      /

      can you please help me to convert to sql 2008

      Reply
  • Jonas Reycian Saraosos
    July 4, 2013 6:50 pm

    that script will work even in other SQL..

    Reply
  • This example was indeed very useful.. thanks guys

    Reply
  • A table-typed variable is useful. But you can also use the XML data type.

    My preference is to use a table-typed variable (it’s just easier to work with), but when you have data where each row does not conform to the same shape and data types, xml is very flexible. It does require learning some XQuery though:

    This will load up an XML-typed variable:

    DECLARE @MyListXML [xml]
    SET @MyListXML = CAST	(	'<root>
    								<item type="varchar(20)"	>a</item>
    								<item type="int"			>10</item>
    								<item type="int"			>20</item>
    								<item type="varchar(20)"	>c</item>
    								<item type="int"			>30</item>
    								<item type="varchar(20)"	>d</item>
    							</root>'	
    						as [xml]
    						);
    

    This will allow you to see it represented as a table:

    SELECT	T.c.query('.')											as [raw_element], 
    		T.c.query('.').value('(/item/@type)[1]', 'varchar(20)') as [data_type], 
    		T.c.query('.').value('(/item)[1]', 'varchar(20)')		as [value]
    FROM	@MyListXML.nodes('/root/item') T(c)
    

    This will allow you to work with only the [int] typed data stored within the XML variable:

    SELECT	T.c.query('.')											as [raw_element], 
    		T.c.query('.').value('(/item/@type)[1]', 'varchar(20)') as [data_type], 
    		T.c.query('.').value('(/item)[1]', 'varchar(20)')		as [value]
    FROM	@MyListXML.nodes('/root/item[@type="int"]') T(c)
    
    Reply
  • How do i do this dynamically.
    For instance:
    Select ID from MyTable Where Fact = 0
    Now, I need to store the list of IDs in a list or in an array so that I can use that list/array in another SQL Statement.
    For instance, Select * from MyAnotherTable Where ID in

    Thank you

    Reply
  • @Prachyut,There can be so many ways.It depend upon exact scenario/requirement.
    Select * from MyAnotherTable Where ID in(Select ID from MyTable Where Fact = 0).
    using CTE is one of them.

    Reply
    • Thank you for response Kumar. I did the same way, the script created seems like it should work, but I haven’t tested it yet, will update after I test it. thank you again

      Reply
  • Hi I am new to sql server..I want to know why we use +#+ in btwn columns and why we take (” as CapaictyPriceDiscountPriceStock”) same in last of every select query..what it represents???…and why we again pass ID (“cast(ID as varchar)”)…Can anyone explain me please????

    ALTER proc [dbo].[Usp_GetProductDetails] –@ProductID=6
    (
    @ProductID int
    )
    As
    begin

    select a.Category_ID, a.Product_ID,a.Product_ImageSmall,
    a.Subcategory_ID,a.Product_Name,a.Product_Code,a.Description,
    a.Payment_Methods,a.Delivery_Area,a.DisplayOnSite,
    a.Product_Image,a.Product_ID,a.Shiping_Details,a.Front_Image,a.Back_Image,a.Rating
    from dbo.tbl_Products_Master as a
    where Product_ID= @ProductID

    –Query for size
    select ID, Size,
    (Unit+’#’+Capacity+’#’+convert(varchar,Price,102)+’#’+convert(varchar,Discounted_Price,102)+’#’+cast(Stock_Qty as varchar)+’#’+cast(ID as varchar)) as ‘CapaictyPriceDiscountPriceStock’
    from tbl_Product_Tran
    where Product_ID= @ProductID and Size=’Small’
    UNION ALL
    select ID, Size,
    (Unit+’#’+Capacity+’#’+convert(varchar,Price,102)+’#’+convert(varchar,Discounted_Price,102)+’#’+cast(Stock_Qty as varchar)+’#’+cast(ID as varchar)) as ‘CapaictyPriceDiscountPriceStock’
    from tbl_Product_Tran
    where Product_ID= @ProductID and Size=’Medium’
    UNION ALL
    select ID, Size,
    (Unit+’#’+Capacity+’#’+convert(varchar,Price,102)+’#’+convert(varchar,Discounted_Price,102)+’#’+cast(Stock_Qty as varchar)+’#’+cast(ID as varchar))
    as ‘CapaictyPriceDiscountPriceStock’
    from tbl_Product_Tran
    where Product_ID= @ProductID and Size=’Large’
    UNION ALL
    select ID, Size,
    (Unit+’#’+Capacity+’#’+convert(varchar,Price,102)+’#’+convert(varchar,Discounted_Price,102)+’#’+cast(Stock_Qty as varchar)+’#’+cast(ID as varchar))
    as ‘CapaictyPriceDiscountPriceStock’
    from tbl_Product_Tran
    where Product_ID=@ProductID and Size=’XS’
    UNION ALL
    select ID, Size,
    (Unit+’#’+Capacity+’#’+convert(varchar,Price,102)+’#’+convert(varchar,Discounted_Price,102)+’#’+cast(Stock_Qty as varchar)+’#’+cast(ID as varchar))
    as ‘CapaictyPriceDiscountPriceStock’
    from tbl_Product_Tran
    where Product_ID= @ProductID and Size=’XL’

    –Query for user commects
    select Name, Comments,convert(varchar(20),Created_on,106) as Created_on from tbl_User_Product_Comments
    where Product_ID=@ProductID and isApproved=’1′

    End

    Reply
    • 1. Your code is using that is used for contatination.
      Select ‘A’+ ‘#’+ ‘B’ should show A#B in output

      2. as CapaictyPriceDiscountPriceStock is called as column alias. Your output would show that as column name.

      Reply
  • Can I use this 2-008 R2 process to do a “Select Not IN? I’m getting an error – Must declare the scalar variable “@ListofIDs”. Here is an example

    DECLARE @ListofIDs TABLE(IDs VARCHAR(100), ID INT IDENTITY(1,1));
    INSERT INTO @ListofIDs
    SELECT ‘MyValue1’
    UNION ALL
    SELECT ‘MyValue2’
    UNION ALL
    SELECT ‘MyValue3’;
    SELECT wsag.[MyData]
    FROM [SQLserver].[dbname].[dbo].[table] wsag
    WHERE wsag.[NAME] Not In @ListofIDs

    Reply
  • Can this technique be applied to finding data that is stored in arrays, not presenting data in arrays. I have some data fields that are multi-value (eg sector = “Gold, Silver, Diamonds, Mining, Metals”) and I want to extract only the rows where the field has a value starting with one specific letter. Like I want to find all rows where sector starts with “M”. Right now I use SELECT * WHERE sector LIKE M% OR sector LIKE %M% and it works, but it looks like this is inefficient. Any better ideas?

    Reply
  • Kiran subramanian
    June 4, 2015 5:01 pm

    DECLARE
    type namesarray IS VARRAY(5) OF VARCHAR2(10);
    type grades IS VARRAY(5) OF INTEGER;
    names namesarray;
    marks grades;
    total integer;
    BEGIN
    names := namesarray(‘Kavita’, ‘Pritam’, ‘Ayan’, ‘Rishav’, ‘Aziz’);
    marks:= grades(98, 97, 78, 87, 92);
    total := names.count;
    dbms_output.put_line(‘Total ‘|| total || ‘ Students’);
    FOR i in 1 .. total LOOP
    dbms_output.put_line(‘Student: ‘ || names(i) || ‘
    Marks: ‘ || marks(i));
    END LOOP;
    END;
    /

    please help me in converting sql 2008

    Reply
  • Nils Schröder
    October 2, 2015 5:23 pm

    That’s interesteing stuff. I always use temp tables like
    create table #ListOfIDs (IDs varchar (100), ID int identity (1,1))

    Where is the tecnical difference between #ListOfIDs and @ListOfIDs besides the syntax?

    Reply

Leave a Reply