How to use Cursor to Execute stored procedure where featurevalue is bigger than 0 ?

Question

I work on SQL server 2012 . I face issue I can't use cursor to execute stored procedure when feature value is bigger than 0 .

I have stored procedure must execute with every line where feature value is bigger than 0

so according to my case I will use cursor to execute stored procedure with feature value that bigger than 0

so EXEC sp_updatevaluesonTables 10,30,40,50

this sp pass to it feature value to do some thing update and insert data

so How to use cursor to execute stored procedure sp_updatevaluesonTables with every value bigger than 0
as
cursor
EXEC sp_updatevaluesonTables 10,30,40,50
end cursor

im not use cursor before so can you help me

create table #updatestatus
(
FeatureValue int,
status nvarchar(50)
)
insert into #updatestatus
values 
(10,NULL),
(50,NULL),
(-2,NULL),
(-3,NULL),
(30,NULL),
(40,NULL)

SELECT * FROM #updatestatus WHERE FeatureValue>0

Exec sp_updatevaluesonTables FEATUREVALUE

Answer 1

I will have to assume that your procedure takes a single parameter, although you list it as it is taking four parameters.

By the way, don't call your procedures sp_something. The sp_ prefix is reserved for system procedure, and would Microsoft ship a system procedure sp_updatevalue in a future release of SQL Server, this is the procedure that would be invoked, not your.

Here is a cursor:

DECLARE @cur CURSOR,  
        @value int  
  
SET @cur = CURSOR STATIC FOR  
   SELECT FeatureValue FROM #updatestatus WHERE FeatureValue > 0  
  
OPEN @cur  
  
WHILE 1 = 1  
BEGIN  
   FETCH @cur INTO @value  
   IF @@fetch_status <> 0 BREAK  
      
   EXEC sp_updatevalue @value  
END  

You declare a cursor variable, and then you assign that cursor with the SET statement. Always use the syntax that I showed you: CURSOR STATIC FOR followed by the SELECT statement to select the data.

The OPEN command is the statement that actually populates the cursor.

You loop over the cursor until you have gotten all rows. The FETCH statement retrieves one row at a time, and the variables in the FETCH statement must match the columns in the SELECT statement that populates the cursor. @@Fetch _status is a function which returns a non-zero value when there are no more rows in the cursor.

If you google around, you will find different ways to run cursors, using a different syntax, and also a different disposition of the WHILE loop. However, I submit that the above is the optimal way to run a cursor.

Answer 2

Don't forget to close and deallocate the cursor...

Answer 3

Do you have any updates？

Echo