Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A stored procedure contains a view which is accessed multiple times with the confines of the SP.

If the SP is executed multiple times in one query window with different parameters, e.g.

EXEC spMySP 1, 2, 3
GO
EXEC spMySP 4, 5, 6
GO

Questions

  • If one assumes that the first call will complete execution before the second one starts and therefore the view within the SP, will it be bound by the connection, session and process?
  • If we were to place each SP execution in its own query window and execute the all, roughly at the same time - we assume they would execute asynchronously?
  • Can we assume that the view would within the SP be operating within a new instance (More a theoretical question)?
share|improve this question

migrated from serverfault.com Aug 28 '11 at 13:38

This question came from our site for professional system and network administrators.

1 Answer 1

up vote 2 down vote accepted

The SPs will be queued and run based on available resources. Depending on the isolation level used in defining the SP and how the transactions are defined, the SPs will wait accordingly. By design, the SP will not always wait for the first SP to finish, and then move onto the second one etc...

This design make is possible for multiple unrelated users to call the SP and work concurrently.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.