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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
December 20, 2020 at 12:04 am

#3822460
How to use Cursor to Execute stored procedure where featurevalue is bigger than 0 ?
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
```

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i see your strategy, simply filter with a WHERE statement to process just the desired rows.

I suspect you just need a decent cursor model? here you go!

create table #updatestatus
 (
 FeatureValue int,
 status nvarchar(50)
 )
 insert into #updatestatus
 values 
 (10,NULL),
 (50,NULL),
 (-2,NULL),
 (-3,NULL),
 (30,NULL),
 (40,NULL)
  
 --desc: cursor model for adapting to times you need a cursor
declare

  @Featurevalueit varchar(64)
  
  declare c1 cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
  --###############################################################################################
  --cursor definition
  --###############################################################################################
    SELECT * FROM #updatestatus WHERE FeatureValue > 0
  --###############################################################################################
  open c1
  fetch next from c1 into @Featurevalue
  While @@fetch_status <> -1
    begin
    Exec sp_updatevaluesonTables @Featurevalue
    
    fetch next from c1 into @Featurevalue
    end
  close c1
  deallocate c1

  
GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!