store list of data into variable store procedure

user_2746693 0
Viewed 607

First of all i excuse me if this question looks very simple for you. Since, i have no idea how to write sp i am posting this question:

Basically i want to write store procedure for and my use case is:

  1. select distinct id from table

    select id from [table_name]

  2. Iterate for all id fetched

  3. Do a query again on another table with this id as parameter

  4. Fetch data

Can anyone please suggest how to achieve this ?

stored-procedures sql-server
2 Answers

SELECT DATA FROM [TABLE2] WHERE TABLE2.TABLE1ID IN (SELECT DISTINCT ID FROM [TABLE1])

Either use inner query concept or inner joins concept.

user_155797800

Based on your question I assume there are no input parameters required for the Stored Procedure.

CREATE PROCEDURE YourProcedureName --change it to your SP name
AS
BEGIN
    
    SET NOCOUNT ON;
    --Choose any approach that suits your case better from bellow and remove the rest approach once you finalize it

    /*1st Approach*/
    SELECT * FROM TABLEB 
    WHERE ID IN (SELECT DISTINCT ID FROM TABLEA)

    /*2nd Approach*/
    SELECT * FROM TABLEB B
    JOIN TABLEA A
      ON B.Id = A.Id

    /*3rd Approach which you are expeting*/
    --Implementing CURSOR
    
    DECLARE @Id INT
    DECLARE Table_Cursor CURSOR FOR 
    SELECT 
    DISTINCT Id FROM TABLEA

    OPEN Table_Cursor  
    FETCH NEXT FROM Table_Cursor INTO @Id 

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
         -- you can insert it into a table if you need
         SELECT * FROM TABLEB WHERE Id = @Id

         FETCH NEXT FROM Table_Cursor INTO @Id
    END 

    CLOSE Table_Cursor  
    DEALLOCATE Table_Cursor

END
GO
user_122516870
Related