How Can One Iterate Over Stored Procedure Results from Within Another Stored Procedure....Without Cursors

How can one iterate over stored procedure results from within another stored procedure....without cursors?

This may not be the most efficient, but I would create a temp table to hold the results of the stored proc and then use that in a join against the target table. For example:

CREATE TABLE #t (uniqueid int)
INSERT INTO #t EXEC p_YourStoredProc

UPDATE TargetTable 
SET a.FlagColumn = 1
FROM TargetTable a JOIN #t b 
    ON a.uniqueid = b.uniqueid

DROP TABLE #t

Stored Procedure - loop through results without cursor

I ended up using a WHILE loop that I can pass each ProductID into a series of checks!!

declare @counter int
declare @productKey varchar(20)

SET @counter = (select COUNT(*) from ##Magento)

while (1=1) 
begin
SET @productKey = (select top 1 ProductKey from ##Magento)
print @productKey;
delete from ##Magento Where ProductKey = @productKey
SET @counter-=1;
  IF (@counter=0) BREAK;

end
go

Stored Procedure - loop through results without cursor

I ended up using a WHILE loop that I can pass each ProductID into a series of checks!!

declare @counter int
declare @productKey varchar(20)

SET @counter = (select COUNT(*) from ##Magento)

while (1=1) 
begin
SET @productKey = (select top 1 ProductKey from ##Magento)
print @productKey;
delete from ##Magento Where ProductKey = @productKey
SET @counter-=1;
  IF (@counter=0) BREAK;

end
go

How can I iterate over a recordset within a stored procedure?

You need to create a cursor to loop through the record set.

Example Table:

CREATE TABLE Customers
(
    CustomerId INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    ,FirstName Varchar(50) 
    ,LastName VARCHAR(40)
)

INSERT INTO Customers VALUES('jane', 'doe')
INSERT INTO Customers VALUES('bob', 'smith')

Cursor:

DECLARE @CustomerId INT, @FirstName VARCHAR(30), @LastName VARCHAR(50)

DECLARE @MessageOutput VARCHAR(100)

DECLARE Customer_Cursor CURSOR FOR 
    SELECT CustomerId, FirstName, LastName FROM Customers

OPEN Customer_Cursor 

FETCH NEXT FROM Customer_Cursor INTO
    @CustomerId, @FirstName, @LastName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @MessageOutput = @FirstName + ' ' + @LastName

    RAISERROR(@MessageOutput,0,1) WITH NOWAIT

    FETCH NEXT FROM Customer_Cursor INTO
    @CustomerId, @FirstName, @LastName
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor

Here is a link to MSDN on how to create them.

http://msdn.microsoft.com/en-us/library/ms180169.aspx

This is why I used Raise Error instead of PRINT for output.

http://structuredsight.com/2014/11/24/wait-wait-dont-tell-me-on-second-thought/

Call procedure for each row without using cursors and loops?

I am not sure of your exact logic to split the string, but if possible you can make your split function an inline TVF (Heres one I made earlier):

CREATE FUNCTION dbo.Split(@StringToSplit NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS TABLE
AS
RETURN
(   
    SELECT  Position = Number,
            Value = SUBSTRING(@StringToSplit, Number, CHARINDEX(@Delimiter, @StringToSplit + @Delimiter, Number) - Number)
    FROM    (   SELECT  TOP (LEN(@StringToSplit) + 1) Number = ROW_NUMBER() OVER(ORDER BY a.object_id)
                FROM    sys.all_objects a
            ) n
    WHERE   SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, n.Number, 1) = @Delimiter
);

Then you can simply use this in your insert statement by using cross apply with the TVF:

DECLARE @T1 TABLE (ID INT IDENTITY, TextToSplit NVARCHAR(MAX) NOT NULL);
DECLARE @T2 TABLE (T1ID INT NOT NULL, Position INT NOT NULL, SplitText NVARCHAR(MAX) NOT NULL);

INSERT @T1 (TextToSplit) 
VALUES ('This is a test'), ('This is Another Test');

INSERT @T2 (T1ID, Position, SplitText)
SELECT  t1.ID, s.Position, s.Value
FROM    @T1 t1
        CROSS APPLY dbo.Split(t1.TextToSplit, N' ') s;

SELECT  *
FROM    @T2;

Looping through records to execute a stored procedure and store the result

You have a few misunderstandings here.

First, you will need to understand that when you use the form insert into table (columns...) exec procedure, the thing that gets inserted is the result set of the stored procedure. In other words, whatever comes out of a select statement inside the procedure. If your stored procedure has no result set, there is nothing to insert. Note that output parameters are not the same as a result set. Observe what happens if you run the following batches:

create table t(result varchar(30))
go

create  procedure p (@result varchar(30) = null output) as 
begin 
    set @result = 'this is the output parameter';
    select 'this is the result set'; 
end
go

declare @param varchar(30);
insert t (result) exec p @param output;
select * from t; 
select @param;

At the end of the execution of this script, the table t will have a single row with the value '"this is the result set". The value of the @param variable will be "this is the output parameter".

OK, now let's look at what's going on in your procedure.

You use the insert ... exec pattern, so the result set from the stored procedure will be inserted as new rows into your @orderInfo table.

But from what you have written, it seems like you intend the price to be provided as an output parameter value to be consumed by your @orderPrice variable. It won't actually do this, because you haven't used it as an output parameter. You left off the output keyword.

I expect your stored procedure has no result set at all. Which means nothing gets inserted into the table.

Even if the stored procedure did return a result set, you are doing an insert. An insert will create new rows, it won't update the value of the OrderPrice column for an existing row.

What you probably intend to be doing is this:

While @ID IS NOT NULL
 BEGIN
   Select @OrderNumber = OrderNumber;
   From @orderInfo Where ID = @ID;
      
   Exec sp_OrderPrice_Calc 
      @Order_Number = @OrderNumber, 
      @Order_Price  = @OrderPrice output; -- output keyword is required both here and in the procedure definition

   update @orderInfo set OrderPrice = @orderPrice where ID = @ID; -- update, not insert.

   Select @ID = MIN(ID) From @OrderInfo where ID > @ID;
 END

There might be an even better solution to use here. If your stored procedure can be rewritten as a user defined function, you wouldn't need a loop at all:

select CompanyName, OrderNumber, DeliveryType, OrderPrice = myOrderPriceFunction(...)
From OrderData;

Iterating without using cursor in MYSQL

Without the use of Cursor, you could iterate using a temporary table and a While..Do statement.

Let's say you have two tables

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

And

CREATE TABLE `tmp_user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

Create the following routine, and adjust the validation process:

DELIMITER $$
USE `routines_sample`$$
CREATE PROCEDURE `nocursor`()
BEGIN
  Declare validId int;
  Declare validName varchar(45);

  -- drop the temporary table if exists
  Drop table if exists `routines_sample`.tmp_validation;
  -- create the temporary table, here you could use Engine=Memory
  Create table `routines_sample`.tmp_validation (`id` int not null, `name` varchar(45) not null, `valid` bit(1) not null) Engine=MyISAM;

  -- insert into the temporary table with a valid flag = 0 (false)    
  Insert into `routines_sample`.tmp_validation (`id`, `name`, `valid`)
  Select tu.id, tu.name, 0
  From `routines_sample`.tmp_user tu;

  -- while exists data to validate on temporary table do something    
  While exists(Select `id` From `tmp_validation` Where `valid` = 0) Do

    Select `id`, `name` Into @validId, @validName From tmp_validation Where `valid` = 0 Limit 1;

    -- do your validation
    Select @validId, @validName;

    -- don't forget to update your validation table, otherwise you get an endless loop    
    Update `tmp_validation` 
    Set `valid` = 1
    Where `id` = @validId;

  END WHILE;

  -- insert only valid rows to your destination table    
  Insert into `routines_sample`.`user` (`name`)
  Select `name` From `tmp_validation`
  Where `valid` = 1;

  -- drop the temporary table    
  DROP TABLE tmp_validation;

END$$

DELIMITER ;

---

Related Topics