Batch vs Stored Procedure

Question

Hi All,

            I have a t-sql batch . I need to schedule this batch for every 30 mins. The batch tsql contains 4 table variable declarations and 4 insert statments. finally 2 selects. My question is whether the plan be re-used every time it calls this batch.  What is the major difference between  calling a stored procedure and calling a batch ?

Any help would be really appreciated.

Thanks 

Chaithanya M

---

Regards Chaithu.. If it is usefull,Mark this as Answer.

Answer 1

Yes and Yes, As long as batch is using the same parameters.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Hi Basit,

                 if so, why cant we use batch batch instead of stored procedure . Why there is trend of using stored procedure instead of batch?

regards

Chaithanya M

---

Regards Chaithu.. If it is usefull,Mark this as Answer.

Answer 3

Well, there are several benifits of using store procedure, such as you can call store porcedure  with in stored  procedure, its easier to version control stored procedure, helps preventing SQL Injection attacks because you can actually validate stored procedure parameters etc etc.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 4

 Execution Plan Retention and Reuse is the main advantage of Sprocs

SP are compiled and their execution plan is cached and used again and again to when the same SP is executed again

---

Miss Never Giveup

Answer 5

Hi Miss,

              Batch is also compiled and their execution plan is cached and uses the same plan on subsequent calls without generating new plan .

Regards

Chaithanya M

---

Regards Chaithu.. If it is usefull,Mark this as Answer.

Answer 6

Chaithanya, you and Never giveup are both correct in one way or other. Batch is also compiling and creating plan based on the parameter passed. So if you are passing different values, different plan would be generated for the batch. However, for sp, its only one plan for the procedure(not talking about the multiple plans for the queries in procs here, that different story). Yes, you will be able to see only plan even for batch for multiple paremeter somecases, thats depends on the parameterization that you set up "Simple, forced".

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 7

A batch is nothing on the server-side, just client-side code. A batch does not have strictly speaking "parameters". Loosely speaking, the embedded local variables in queries are considered "parameters" in the batch.

A stored procedure is a compiled server-side object.

That is the big difference.

You should make the batch into a stored procedure if for regular use:

http://www.sqlusa.com/bestpractices2008/stored-procedure-parameters/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 8

I also think that a procedure has a good chance for plan reuse or say it promote plan use because the plan reuse is based on the proc name..On the other hand a Batch has a less reuse chance..If it is an ADHOC batch the reuse is based on the exact match and same SET options also.A batch getting parametrized also depends on various factors and forcing a database to run in forced paramtrization is also not considered efficient.

For detail read this : http://msdn.microsoft.com/en-us/library/ee343986%28v=SQL.100%29.aspx

---

Thanks and regards, Rishabh K

Answer 9

Yes and Yes, As long as batch is using the same parameters.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

I guess a batch can also be cached without any parameter.

---

Thanks and regards, Rishabh K