quote:

---

On creation it will syntax check and try to resolve some objects.
If the objects are not there it may give an error (columns), warning (stored procs) or success return (tables).

On first run it will generate an execution plan - which may or may not resolve all the objects but will fail if it tries to access non-existent objects.
The execution plan consists of a query plan and an execution contex which is specific to the data with which the SP is being called.
This is held in memory for subsequent calls until a used object is changed or the data distribution changes and forces a recompile or it is purged.
Execution plans can be recreated on each call using the recompile option or if objects are transient (temp tables) and not created within the SP.

Note that the plan can be confused by changing objects in another database as it may not spot the change and the object ID referenced may now point to an incorrect object.


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

---

Thanks Mr nr for help. But I have problem when using recompile option, I don't understand what is the defferent between re create execution plan and recompile procedure. (I don't uderstand the meaning of WITH RECOMPILE option) I saw there is no defferent in result of a store procedure when I use or not use RECOMPILE option. For example I have a store procedure :

Create proc proc1

@para1 int = Null,
@para2 int = Null

as

Insert testtable values (@para1,@para2)

Select * from testtable

Go

Fist I not use WITH RECOMPILE in SP, and I call SP with

Exec proc1 12,12
I'll get a new row of values (12,12)

recall SP with
Exec proc1 15,15
I get a new row of values (15,15) insert to testtable too.

Then I add WITH RECOMPILE in SP and try to call SP two times with different values for each time. I get the result as the first time, when I not use WITH RECOMPILE option. Ummmm I really don't understand when to use RECOMPILE and when not.

Thanks for your attention