Problem
- Hello Everyone,
I have some doubts about store procedures.
Ex:
I have one store procedure, which have 100 statements in it. Now i am changing the line number 15 in that store procedure. Is it complie fully again or it will complie only the changed line.
Plz help.
-
To my knowledge, stored Procedures are compiled every time you alter and run the SQL of the procedure. What is your concern or doubt?
If the phone doesn't ring...It's me.
-
The entire proc is recompiled. Recompilation occurs for a variety of reasons
a) You make a change
b) SQL Server determines that it is worth recompiling due to changes in databased indices, etc
c) You have the "with recompile" switch set as part of your create procedure statement
d) Many other reasons
But don't be worried about that - it's a transparent process. It doesn't take a long time and is better than your app sending one hundred lines of text down the wire, letting SQL compile it (or try to reuse a plan) and then send back your data.. With the SP you just send down that you want procedure X executed and SQL can immediately see if it has a query plan and make use of it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply