CODEWITHSUNDEEP
sql-server
learner
learner

Reputation: 21

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

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

Upvotes: 0

Views: 1496

Answers (2)

EzLo
EzLo

Reputation: 14189

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

Upvotes: 2

gbn
gbn

Reputation: 432271

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

Upvotes: 0

Related Questions