Please tell me difference between running query directly and executing it using exec

Question

Please tell me what is difference ==> if i write query directly in storedprocedure ==> and write query in string variable and than run it in exec in stored procedure.

i am using ms sql server 2005

Answer 1

Diff:

• With exec statement you can create execute query which is created dynamically, stored in a variable [You have to use this in some cases].

Answer 2

There's lots of fun information in the remarks section of BOL, such as:

Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this following statement is run, the database context is master.

USE master; EXEC ('USE AdventureWorks; SELECT EmployeeID, Title FROM HumanResources.Employee;');

Answer 3

EXEC commands with string literals is error-prone and insecure (SQL injection) since the execute just executes whatever you give it.

Check the security notice: http://msdn.microsoft.com/en-us/library/ms188332.aspx

Answer 4

With some exceptions EXEC('sql stmnt') is what you use when you have no other choice.

It allows you to dynamically build a statement and execute it, which is often the only way of achieving something when object names are variable and not known in advance.

Read this article on dynamic SQL which explains scenarios when/why dynamic SQL is useful & goes into detail about EXEC().

As for the differences between running an SQL statement in a stored procedure and running it in the procedure as EXEC(@SQL_STRING):

• None of the objects referenced in @SQL_STRING will be checked
• None of the T-SQL code will be verified for syntax and type checking
• Stuff in @SQL_STRING is within its own scope relative to the SP
• You risk being careless and poorly forming @SQL_STRING which can lead to security problems.
• The query plan for @SQL_STRING will be cached but only reused if a subsequent EXEC(@SQL_STRING) matches it exactly, with an SP a single query plan can be reused if all that changes are parameters.