SQLTeam.com | Weblogs | Forums

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

Transact-SQL
,
#1

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
#2

What have tried?

#3

really I don't use cursor before so if you can tell me
How i do it

#4

so can you help me please ?

#5

It is not recommended to use cursor but I encourage you to try something first on your own first then show us what you tried from following example

1 Like
#6

The better option would be to rewrite the procedure to accept a set of features and perform the work all at once - however, that isn't always possible and you need to use a cursor to loop over your result set and call the procedure for every item.

There are plenty of examples of writing a cursor...one thing to be aware of is that for this type of execution you want to make sure the cursor is static, read only and forward only. This will reduce the impact of the cursor as it won't need to hold locks on the source tables.

This can also be accomplished without a cursor - using either a WHILE loop or generating the code to be executed as a string and executing that code using EXEC or sp_executeSql.