Update only one row using a stored procedure with a cursor, each time the stored procedure is called

Question

I am very new to using cursors. I'm trying to make only one row change the freight value (not all), each time the stored procedure is ran. Any help would be greatly appreciated.

[Code]

SELECT * FROM NorthWind.dbo.Orders ORDER BY ShipVia, Freight --Checking...
IF (OBJECT_ID('spUpateOrder')IS NOT NULL) DROP PROCEDURE spUpateOrder;
GO
CREATE PROCEDURE spUpateOrder
 @CustomerID nchar(5)
,@EmployeeID [int]
,@OrderDate datetime = NULL
,@RequiredDate datetime = NULL
,@ShippedDate datetime = NULL
,@ShipVia int = 1  -- may have a primary shipper
,@Freight money
,@ShipName nvarchar(40) = NULL
,@ShipAddress nvarchar(60) = NULL
,@ShipCity nvarchar(15) = NULL
,@ShipRegion nvarchar(15) = NULL
,@ShipPostalCode nvarchar(10) = NULL
,@ShipCountry nvarchar(15) = NULL
AS

DECLARE UpdateOneRowCur CURSOR LOCAL FOR

SELECT Freight FROM [NorthWind].[dbo].[Orders] -- WHERE Freight = NULL
WHERE CustomerID = 'alfki' AND Freight < 30.00 AND Freight IS NOT NULL
SET @Freight = @Freight + .1
OPEN UpdateOneRowCur

FETCH NEXT FROM UpdateOneRowCur INTO @Freight
WHILE @@FETCH_STATUS = 0 
BEGIN
    EXEC spUpateOrder
     @CustomerID
    ,@EmployeeID 
    ,@OrderDate 
    ,@RequiredDate 
    ,@ShippedDate 
    ,@ShipVia  --= 1,  -- may have a primary shipper
    ,@Freight 
    ,@ShipName 
    ,@ShipAddress 
    ,@ShipCity 
    ,@ShipRegion 
    ,@ShipPostalCode 
    ,@ShipCountry 
    FETCH NEXT FROM UpdateOneRowCur INTO @Freight


    RETURN @@Identity
    DECLARE @Ret int
    EXECUTE @Ret = @Freight;
    --EXECUTE  @Ret = spUpateOrder --'alfki', 7, '1/1/2013', null, null, 1
                                     , null;
    IF @ret = 0 
        PRINT 'error!';
    ELSE 
        PRINT 'OrderId entered: ' + CAST(@ret as varchar);

    CLOSE UpdateOneRowCur
    DEALLOCATE UpdateOneRowCur
    END;
    GO
    Declare @Ret int
    EXEC  @Ret = spUpateOrder 'alfki', 7, '1/1/2013', null, null, 1, 25.99;

    GO

[/Code]

This code does change all the rows at once, but I only want it to change the first row it finds and then the second row it finds next time I run the stored procedure. Not sure how to do this? Note: I made some new rows with the same freight value 25.99 in order to test it.

Answer:

Here is what I did to change only one row each time the stored procedure was executed: (This does work well!)

 [code]
 --============== UPdate only one row ===============--

 CREATE PROCEDURE uspUpdateOrder
 @Freight money --Testing.

 AS
 /*
 Created by: Chris Singleton
 02/26/2017
 Updates each row that has 25.99 where the customer's name is 'alfki'. 
 */
 BEGIN
    --DECLARE @Freight

    UPDATE top (1) Northwind.dbo.Orders 
    SET Freight = @Freight + .1 
    WHERE CustomerID = 'alfki' AND Freight = 25.99

  END;

  --============ Call the Stored Procedure =============--
 GO
 -- the call:
 Declare @Ret int

 EXEC  @Ret = uspUpdateOrder 25.99;
 If @ret = 0 
   print 'error!';
 else 
    print 'OrderId entered: ' + cast(@ret as varchar);

 DROP PROCEDURE uspUpdateOrder
 GO
 SELECT * FROM [Northwind].[dbo].[Orders] WHERE CustomerID = 'alfki'

 --====================================--
 [/code]

Answer 1

Sparrow is right in a way, that I don't need a cursor to do the coding, so I gave Sparrow points, but in order to focus on one row with filters, you can do this.

[Code]
--============== UPdate only one row ===============--

CREATE PROCEDURE uspUpdateOrder
@Freight money --Testing.

AS
/*
Created by: Chris Singleton
02/26/2017
Updates each row that has 25.99 where the customer's name is 'alfki'. 
*/
BEGIN
--DECLARE @Freight

UPDATE top (1) Northwind.dbo.Orders 
SET Freight = @Freight + .1 
WHERE CustomerID = 'alfki' AND Freight = 25.99

END;

--============ Call the Stored Procedure =============--
GO
-- the call:
Declare @Ret int

EXEC  @Ret = uspUpdateOrder 25.99;
If @ret = 0 
print 'error!';
else 
print 'OrderId entered: ' + cast(@ret as varchar);

DROP PROCEDURE uspUpdateOrder
GO
SELECT * FROM [Northwind].[dbo].[Orders] WHERE CustomerID = 'alfki'

--====================================--

[/Code]

Note: That you can focus on the row using a combination of the update clause TOP (1) and the where clause as a filter. This work's nicely!