Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_executesql vs exec Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2012 3:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:48 AM
Points: 63, Visits: 198
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.
Post #1244180
Posted Tuesday, January 31, 2012 3:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 9:59 AM
Points: 28, Visits: 59
All these are available in BOL.
Post #1244188
Posted Tuesday, January 31, 2012 3:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
exec or execute

http://msdn.microsoft.com/en-us/library/ms188332.aspx

sp_executesql

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

simples.


There's no kill switch on awesome!
Post #1244193
Posted Tuesday, January 31, 2012 3:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 1,337, Visits: 2,627
deepikamm (1/31/2012)
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.


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:

EXEC/Execute

SP_ExecuteSQL

Dynamic SQL



Sujeet Singh
Post #1244194
Posted Tuesday, January 31, 2012 3:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:48 AM
Points: 63, Visits: 198
Thank you all for quick response.

I am expecting layman explanation on these topics and I am tired of reading msdn.
Post #1244203
Posted Tuesday, January 31, 2012 3:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:48 AM
Points: 63, Visits: 198
To be more precise,I am expecting blogs or articles on these topics.

(plz don't direct me to microsoft blogs)
Post #1244205
Posted Tuesday, January 31, 2012 3:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 1,337, Visits: 2,627
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.



Sujeet Singh
Post #1244207
Posted Tuesday, January 31, 2012 3:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 1,337, Visits: 2,627
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)


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 ).

http://www.sqlservercentral.com/articles/nHibernate/86913/



Sujeet Singh
Post #1244209
Posted Tuesday, January 31, 2012 4:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:48 AM
Points: 63, Visits: 198
after seeing sp_executesql in sqlservercentral homepage,I started searching about it.
Post #1244216
Posted Tuesday, January 31, 2012 4:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 1,337, Visits: 2,627
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 .



Sujeet Singh
Post #1244219
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse