locked
Calling Stored Procedure within St Procedure into loop(looping 7 times) Sp gone in Looping RRS feed

  • Question

  • Calling Stored Procedure within St Procedure into loop(While looping --7 times)  Sp gone in Looping .................

    Create Proc TP

    As

    Declare t int,a varchar(20),b varchar(20)

    set t=7

    While (t>1)

    begin

    print 1

    select  @a=a ,@b=b from table1

    EXEC USP_INSERTStoredProcedure @a,@b

    end

    when i execute it gone into loop.

     

     

    Sunday, July 17, 2011 11:45 AM

Answers

  • No doubt it will go in indefinite loop... You need to have

    set t=t-1 

     

    Create Proc TP
    
    As
    
    Declare @t int
    Declare @a varchar(20),@b varchar(20)
    
    set @t=7
    
    While (@t>1)
    
    begin
    
    print 1
    
     select 1 from sys.objects
     set @t=@t-1
    end
    


     


    MCITP, MCTS, MCDBA,MCP
    • Proposed as answer by Mr. Wharty Sunday, July 17, 2011 10:42 PM
    • Marked as answer by Alex Feng (SQL) Sunday, July 24, 2011 8:21 AM
    Sunday, July 17, 2011 12:19 PM

All replies

  • No doubt it will go in indefinite loop... You need to have

    set t=t-1 

     

    Create Proc TP
    
    As
    
    Declare @t int
    Declare @a varchar(20),@b varchar(20)
    
    set @t=7
    
    While (@t>1)
    
    begin
    
    print 1
    
     select 1 from sys.objects
     set @t=@t-1
    end
    


     


    MCITP, MCTS, MCDBA,MCP
    • Proposed as answer by Mr. Wharty Sunday, July 17, 2011 10:42 PM
    • Marked as answer by Alex Feng (SQL) Sunday, July 24, 2011 8:21 AM
    Sunday, July 17, 2011 12:19 PM
  • Although the reply from Madhu is technically correct, what are you trying to achieve with this structure?
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Sunday, July 17, 2011 10:42 PM