2

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 ?

Rohitesh
  • 1,514
  • 7
  • 28
  • 51

2 Answers2

0

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

Either use inner query concept or inner joins concept.

Shravan Samboju
  • 1
  • 1
0

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
Annamalai D
  • 859
  • 1
  • 7
  • 21
  • Yes it will. This is for MSSQL, I developed it in SSMS. Just modify the table names and decide the approach you would like to use and comment out or remove other section before creating the SP. – Annamalai D Apr 08 '21 at 06:34
  • Why even suggest a cursor, there is almost no scenario when it is useful – Charlieface Apr 08 '21 at 09:21
  • I was suggesting multiple approaches. Not enforcing to use cursors. I beleive there is no harm in knowing about cursors to iterate. Please Correct me if I'm wrong. – Annamalai D Apr 08 '21 at 09:56
  • https://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server – Charlieface Apr 11 '21 at 19:28
  • Yeah, I agree there are some performance issue in using cursor, but if you want to iterate on a table's column then you have to use cursors/while loop to do it, no other way you can achieve this. – Annamalai D Apr 12 '21 at 02:39