none
How to iterate in data when data return by store procedure RRS feed

  • Question

  • please guide me that when a SP return tabular data then how could i iterate in that data without dumping that data into temp table? looking for advise. SP return a tabular data where no unique ID exist.
    Friday, October 18, 2019 12:58 PM

Answers

  • The question is: How does the procedure return the data.

    If you mean data returned by the procedure issuing internally a SELECT, then the only option is to use INSERT INTO ... EXEC with a temporary table.

    You should consider using either a function returning a table or using (keyed) temporary tables.

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 1:06 PM
  • You can't.  Your only option with a SP is to output it to a table.

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 1:16 PM
    Moderator
  • Hi Sudip_inn,

    You can use a table variable for your scenario: Table Variable (Transact-SQL)

    "...To declare variables of type table, use DECLARE @local_variable..."

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 1:22 PM
  • Hi 

    Please go through the below code and change it based on your need.

    create procedure p_x
    as
    begin
    declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
    insert @t values('a', 1,1,1)
    insert @t values('b', 2,2,2)
    
    select * from @t
    end
    go
    
    declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
    insert @t
    exec p_x
    
    select * from @t

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 2:29 PM

All replies

  • The question is: How does the procedure return the data.

    If you mean data returned by the procedure issuing internally a SELECT, then the only option is to use INSERT INTO ... EXEC with a temporary table.

    You should consider using either a function returning a table or using (keyed) temporary tables.

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 1:06 PM
  • You can't.  Your only option with a SP is to output it to a table.

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 1:16 PM
    Moderator
  • Hi Sudip_inn,

    You can use a table variable for your scenario: Table Variable (Transact-SQL)

    "...To declare variables of type table, use DECLARE @local_variable..."

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 1:22 PM
  • Hi 

    Please go through the below code and change it based on your need.

    create procedure p_x
    as
    begin
    declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
    insert @t values('a', 1,1,1)
    insert @t values('b', 2,2,2)
    
    select * from @t
    end
    go
    
    declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
    insert @t
    exec p_x
    
    select * from @t

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Sudip_inn Tuesday, November 5, 2019 8:03 AM
    Friday, October 18, 2019 2:29 PM
  • Hi Sudip_inn,

    Would you like to return data from a Stored Procedure? Please refer to this article :Return Data from a Stored Procedure. it will share you three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes. 

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 21, 2019 5:58 AM
  • how to iterate in data return by SP using cursor ? is it possible. if yes then share some sample code. thanks
    Monday, October 21, 2019 10:39 AM
  • how to iterate in data return by SP using cursor ? is it possible. if yes then share some sample code. thanks
    Monday, October 21, 2019 10:39 AM
  • how to iterate in data return by SP using cursor ? is it possible. if yes then share some sample code. thanks
    Monday, October 21, 2019 10:39 AM
  • how to iterate in data return by SP using cursor ? is it possible. if yes then share some sample code. thanks
    Monday, October 21, 2019 10:39 AM
  • how to iterate in data return by SP using cursor ? is it possible. if yes then share some sample code. thanks
    Monday, October 21, 2019 10:39 AM
  • Hi

    Here it goes.

    create procedure p_x
    as
    begin
    declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
    insert @t values('a', 1,1,1)
    insert @t values('b', 2,2,2)
    
    select * from @t
    end
    go
    
    declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
    insert @t
    exec p_x
    
    DECLARE 
        col1 VARCHAR(MAX), 
        col2   DECIMAL;
     
    DECLARE cursor_product CURSOR
    FOR SELECT 
            * FROM @t;
     
    OPEN cursor_product;
     
    FETCH NEXT FROM cursor_product INTO 
        @product_name, 
        @list_price;
     
    WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT @product_name + CAST(@list_price AS varchar);
            FETCH NEXT FROM cursor_product INTO 
                @product_name, 
                @list_price;
        END;
     
    CLOSE cursor_product;
     
    DEALLOCATE cursor_product;
    

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, October 21, 2019 11:02 AM