Store output of a select to a variable array in SQL Server

Question

I have a table called Structure1 in that I want to store Structure_name values into an array and compare with another table Structure2 with column item and display the values if matching.

    DECLARE  @Structure1  TABLE
    (
        Structure_name VARCHAR[128]
    )

    SELECT Structure_name FROM Structure1

    SELECT COUNT(*) INTO xyz FROM Structure1 
    
    FOR i=1..xyz
        SELECT Structure_name FROM Structure2 WHERE ITEM = Structure[i]
        PRINT Structure_name values..
    END FOR

EzLo · Accepted Answer · 2018-06-27 12:29:27Z

Seems that you come from another programming language background. In most (if not all) relational databases, your operations are done in a set basis, not one by one. They are optimized to work that way also.

You can simply join both tables and display all the matching values.

SELECT
    S2.Structure_name
FROM
    Structure2 AS S2
    INNER JOIN Structure1 AS S1 ON S2.Structure_name = S1.Structure_name

EDIT: If you really want to use looping, you can use a CURSOR. Basic syntax is like the following:

DECLARE StructureCursor CURSOR FOR
    SELECT
        S.Structure_name
    FROM
        Structure2 AS S

OPEN StructureCursor

DECLARE @StructureName VARCHAR(200)

FETCH NEXT FROM StructureCursor INTO @StructureName

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @MatchingStructureName VARCHAR(200)

    SELECT
        @MatchingStructureName = S1.Structure_name
    FROM
        Structure1 AS S1
    WHERE
        S1.Structure_name = @StructureName

    PRINT (@MatchingStructureName)

    FETCH NEXT FROM StructureCursor INTO @StructureName

END

CLOSE StructureCursor
DEALLOCATE StructureCursor

gbn · Accepted Answer · 2018-06-27 12:01:16Z

0

This is what JOINs do in RDBMS

This gives all matching rows between the 2 tables

SELECT
   *
FROM
  @Structure1 s1
  JOIN
  Structure2 s2 ON s1.Structure_name  = s2.ITEM

answered Jun 27, 2018 at 12:01

gbn

435k85 gold badges602 silver badges690 bronze badges

3 Comments

learner Over a year ago

i want to use array concept and do ..but in mssql there is no array concept, so am using temporary tables and want to do...

learner Over a year ago

have to do many validations ..so need to use this array concept. thanks.

gbn Over a year ago

@learner: There are very few cases where you need to loop in SQL or "use arrays". We also answered what you asked. If you want help on your real problem, then give us the real problem.

Collectives™ on Stack Overflow

Store output of a select to a variable array in SQL Server

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related