diff Sp_executesql and Exec(sql) ?

Question

diff Sp_executesql and Exec(sql) ?

fuleye

SSC Rookie

Points: 30
More actions
February 26, 2008 at 2:03 am

#182558

Hie,
what is the difference between Sp_Executesql and Exec(sql)?
can some one please guide me ?
thanks in advance
Milan Suman.
"If you Dream it, You can Do it......."

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

sp_executesql can be parameterized. This is an advantage when you are executing the same statement meny times but with different parameter values. In this case, the execution plan for the parameterized statement is likely to be reused.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

fuleye SSC Rookie Points: 30 More actions · Answer 2

Hie Andras,

If you can explain me more about this with one example.

I will be more happy.

Thanks,

Milan Suman

"If you Dream it, You can Do it......."

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

When you execute dynamic sql via the sp_executesql method it is parameterized. This means that you can declare variables, within the scope of the dynamic sql. This leads to better performance and query plan reuse. On the other hand, if you use exec ('some sql string'), a new query plan will have to be created and the sql statement has to be compiled each time.

sp_executeSQL should always be used over the exec method because by parameterizing your dynamic SQL you help performance and reduce the risk of SQL injection attack.

More info:

http://technet.microsoft.com/en-us/library/ms188001.aspx

My blog: http://jahaines.blogspot.com