Have Geek Brain

Please tell me the difference between running the request directly and executing it with exec

Please tell me what's the difference ==> if I write the query directly in the stored procedure ==> and write the query in a string variable and then run it in exec in the stored procedure.

I am using ms sql server 2005

+2


a source to share


4 answers


With a few exceptions, EXEC('sql stmnt')

this 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 to achieve anything when the object names are variable and not known in advance.

Read this article article about dynamic SQL, which explains the scenario where / why dynamic SQL is useful and describes in detail EXEC()

.



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

:

  • None of the referenced objects @SQL_STRING

    will be checked
  • None of the T-SQL code will be checked for syntax and type checking
  • Material in @SQL_STRING

    is within its own area relative to SP
  • You run the risk of being sloppy and poorly shaped @SQL_STRING

    , which can lead to security problems.
  • The query plan for @SQL_STRING

    will be cached, but only reused if the next one EXEC(@SQL_STRING)

    exactly matches it, while the SP can be reused one query plan if all changes are parameters.
+2


a source


Diff:



  • With the exec statement you can create an execution request that is generated dynamically, stored in a variable [you should use this in some cases].
0


a source


There is a lot of interesting information in the BOL notes section , for example:

Changes only to the database context until the end of the EXECUTE expression. For example, after EXEC in this next statement is executed, the database context is the master.

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


a source


EXEC commands with string literals are error prone and unsafe (SQL injection) because execution just does whatever you give it.

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

0


a source






More articles:


All Articles