January 31, 2012 at 3:25 am
Kindly explain the following
EXEC
sp_executesql
dynamic sql
exec vs sp_executesql
I am not sure whether i can ask all the questions in a single thread.I am sorry if i have breached the posting rules of sqlservercentral.:-)
January 31, 2012 at 3:36 am
All these are available in BOL.
January 31, 2012 at 3:40 am
exec or execute
http://msdn.microsoft.com/en-us/library/ms188332.aspx
sp_executesql
http://msdn.microsoft.com/en-us/library/ms188001.aspx
simples.
January 31, 2012 at 3:44 am
deepikamm (1/31/2012)
Kindly explain the followingEXEC
sp_executesql
dynamic sql
exec vs sp_executesql
I am not sure whether i can ask all the questions in a single thread.I am sorry if i have breached the posting rules of sqlservercentral.:-)
1. EXEC : EXEC/Execute is used to execute any stored procedure or character string. Mostly it is used to execute the stored procedure.
2. SP_ExecuteSQL: SP_ExecuteSQL is used to execute ad-hoc SQL statements so that they can be executed as parameterized statements. It helps to boost the performance of the server as same statements are not frequently compiled.
3. Dynamic SQL: It is when we preapare the SQL statements on the fly. Most of the times SP_ExecuteSQL is used to execute dynamic SQL.
4. EXEC vs SP_ExecuteSQL : Differences given above.
For further reading click on below links:
January 31, 2012 at 3:51 am
Thank you all for quick response.
I am expecting layman explanation on these topics and I am tired of reading msdn.
January 31, 2012 at 3:53 am
To be more precise,I am expecting blogs or articles on these topics.
(plz don't direct me to microsoft blogs:w00t:)
January 31, 2012 at 3:54 am
deepikamm (1/31/2012)
Thank you all for quick response.I am expecting layman explanation on these topics and I am tired of reading msdn.
I think we have provided the layman definition. If you want anything special OR if we were not clear at any point about these keywords you may ask that as well.
January 31, 2012 at 3:59 am
deepikamm (1/31/2012)
To be more precise,I am expecting blogs or articles on these topics.(plz don't direct me to microsoft blogs:w00t:)
Every msdn article has provided well written examples also so they are the primary source to read. However, the third link in my first post is a good article about SP_ExecuteSQL. A good article on the same topic is todays topic on the SQLServerCentral home page (you missed :-P).
http://www.sqlservercentral.com/articles/nHibernate/86913/
January 31, 2012 at 4:09 am
after seeing sp_executesql in sqlservercentral homepage,I started searching about it.
January 31, 2012 at 4:16 am
deepikamm (1/31/2012)
after seeing sp_executesql in sqlservercentral homepage,I started searching about it.
Seeing !!!? Didn't you read the article?
David shows the use of SP_ExecuteSQL when he executes this code in his article:
USE AdventureWorks
go
DECLARE
@EmployeeID int,
@Statement nvarchar(2000),
@ParamDefinitions nvarchar(2000)
SET @Statement = N'SELECT * FROM HumanResources.Employee WHERE ManagerId=@ManagerID AND MaritalStatus=@MaritalStatus'
SET @ParamDefinitions = N'@ManagerID INT,@MaritalStatus char(1)'
exec sp_executesql @statement, @ParamDefinitions,@ManagerID=21,@Maritalstatus='S'
He clearly explains how SP_ExecuteSQL helps to use the plan stored in the cache even after chainging the parameter values :cool:.
February 2, 2012 at 9:19 am
Laymens terms (from the articles listed above):
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.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 2, 2012 at 10:26 pm
Thank you mate:-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply