January 6, 2011 at 3:57 am
what is the major difference between EXEC and sp_executesql?
January 6, 2011 at 4:02 am
deepikamm (1/6/2011)
what is the major difference between EXEC and sp_executesql?
they are the same....
Dave explains is very nicely...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 6, 2011 at 4:19 am
Hi...
sp_executesql is a system stored procedure that you can use in place of EXEC to execute your dynamic sql.
sp_executesql allows you to create parameterized queries
Try this link also
January 6, 2011 at 4:24 am
Henrico Bekker (1/6/2011)
deepikamm (1/6/2011)
what is the major difference between EXEC and sp_executesql?they are the same....
Dave explains is very nicely...
No, both are different. And the link you provided shows the difference between EXEC and EXECUTE.
Straight from BOL
sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
Being able to substitute parameters in sp_executesql offers the following advantages to using the EXECUTE statement to execute a string:
- Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.
- The Transact-SQL string is built only one time.
- The integer parameter is specified in its native format. Casting to Unicode is not required.
The biggest advantage is that sp_executesql provides a much highe level of protection against SQL injection than EXECUTE does when dealing with parameter provided by an application (e.g. entered by user).
January 6, 2011 at 4:29 am
thank u so much..
January 6, 2011 at 4:31 am
Also
The primary difference between both is that Exec statement is Unparameterised whereas sp_executeSql is Parameterised.
If we use sp_executeSql, the cached plan would be created only once and would be reused 'n' number of times for ‘n’ number of parameters.
So this would have better performance.
Using Exec, 2 separate execution plans will be created.
But when we are using sp_executesql, the execution plan will be created only once and will be reused for the 2 parameters and hence the time would be saved in this.
January 6, 2011 at 4:41 am
thank u ..simple and clear explanation.
consider this,
Original query
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid=@WMPID
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in
(select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid=@WMPID)
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in
(select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid=@WMPID)
Modified query
update workmgmtplan set IsWebPlannerTracked=1 where planid in
(
Select @WMPID
Union
select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid= @WMPID
Union
select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid= @WMPID
)
Is it possible to replace UNION with CASE expression to obtain the same result?
January 6, 2011 at 4:44 am
deepikamm (1/6/2011)
thank u ..simple and clear explanation.consider this,
Original query
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid=@WMPID
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in
(select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid=@WMPID)
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in
(select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid=@WMPID)
Modified query
update workmgmtplan set IsWebPlannerTracked=1 where planid in
(
Select @WMPID
Union
select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid= @WMPID
Union
select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid= @WMPID
)
Is it possible to replace UNION with CASE expression to obtain the same result?
What exactly this post has to do with EXECUTE vs. sp_executeSql :doze::ermm:
January 6, 2011 at 4:50 am
actually i posted it in different forum..but i didnt get a reply..dats y posted here.
January 6, 2011 at 4:55 am
deepikamm (1/6/2011)
actually i posted it in different forum..but i didnt get a reply..dats y posted here.
It's best that you create a specific thread if your question is not related to your previous question.
The reason why you maybe didn't get an answer in the other forum, is because the SQL statement is hard to read.
Throw some formatting in, use the SQL IFCode shortcut to have some color coding and voila, it's much more pleasant to read and people will be more willing to help you.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 5:04 am
hmm
January 6, 2011 at 5:06 am
deepikamm (1/6/2011)
actually i posted it in different forum..but i didnt get a reply..dats y posted here.
You posted about an hour ago on the other thread.
I strongly recommend to lower your expectation regarding the response time on a forum post...
And, als already stated by Koen, you might want to help us help you by providing sufficient information in a consumable format.
Additionally to Koens advice I recommend you take at look at the first link in my signature on how to post questions on a forum...
January 6, 2011 at 5:08 am
deepikamm (1/6/2011)
hmm
Another advice: once you posted something, don't delete or replace it by a comment like you just did. Stick to what you posted. Deleting posts is not following forum etiquette.
January 6, 2011 at 5:10 am
ok
January 6, 2011 at 8:30 am
Henrico Bekker (1/6/2011)
deepikamm (1/6/2011)
what is the major difference between EXEC and sp_executesql?they are the same....
Dave explains is very nicely...
EXEC and EXECUTE are the same, but the question was between EXEC and sp_executesql, which are not the same, at all.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply