none
SQL SP calling another SQL SP

    Question

  • Hi,

    Hope you all doing well!!!!

    I have to do some tricky stuffs in SQL.

    I have two stored procedure, i suppose to call second stored  procedure from first Stored procedure.

    i want to know, once i called another sp, the control will come back to former sp after second sp execution completed or it will come back to former sp after just calling the second sp. 

    Note : If control will come to the former sp after second sp exection completed, please let me know how to just call a sp and proceed further rather to wait for second sp execution  to complete.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Monday, April 29, 2013 2:33 PM

Answers

  • SQL execute the code line by line.. in other words the statement after the 2nd sp only gets executed after completing the 2nd sp.

    vt 


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, April 29, 2013 2:37 PM
  • Hello Amit,

    In SQL Server / T-SQL everything is executed sequentially & syncron, so your control flow in SP1 will wait until the execution of SP2 is finished.

    There is no direct way to execute a stored procedure asynchronously; you could only use a SQL Server Service Broker to achieve this. (send message in queue and then SB activates a stored procedure).


    Olaf Helper

    Blog Xing

    Monday, April 29, 2013 2:38 PM

All replies

  • SQL execute the code line by line.. in other words the statement after the 2nd sp only gets executed after completing the 2nd sp.

    vt 


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, April 29, 2013 2:37 PM
  • Hello Amit,

    In SQL Server / T-SQL everything is executed sequentially & syncron, so your control flow in SP1 will wait until the execution of SP2 is finished.

    There is no direct way to execute a stored procedure asynchronously; you could only use a SQL Server Service Broker to achieve this. (send message in queue and then SB activates a stored procedure).


    Olaf Helper

    Blog Xing

    Monday, April 29, 2013 2:38 PM
  • Hi Amit,

    This is not the logical way to call child SP within master SP and put it on hold. If you requires the use of child SP, just call it at appropriate step. And also you may aware this approach is not good in performance view point. As other also replied that SQL compiles the code line-by-line.


    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

    Monday, April 29, 2013 6:52 PM