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

• Lowell

  SSC Guru

  Points: 323527

  More actions

  December 20, 2020 at 1:13 pm

  #3822565

  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!