The difference between basic syntax--exec and EXEC sp_executesql for dynamic SQL statements

Http://www.cnblogs.com/goody9807/archive/2010/10/19/1855697.html

Dynamic SQL statement Basic syntax

1: Normal SQL statements can be executed with exec
Eg:select * from TableName

Exec (' select * from TableName ')

Exec sp_executesql n ' select * from TableName '--please note that the string must be added n before

2: Field name, table name, database name, etc. as variables, you must use dynamic SQL
eg

DECLARE @fname varchar Set @fname = ' Filedname '

Select @fname from TableName-error, does not prompt for errors, but the result is fixed value filedname, not required.

Exec (' select ' + @fname + ' from TableName ')--pay attention to the space on the edge of the single quotation mark before and after the plus sign
Of course, changing the string to a variable can also

DECLARE @fname varchar Set @fname = ' Filedname '--set field name
DECLARE @s varchar (+) Set @s = ' SELECT ' + @fname + ' from TableName ' Exec (@s) --Success

EXEC sp_executesql @s--This sentence will be an error
DECLARE @s Nvarchar (1000)--note here to Nvarchar (1000)

Set @s = ' SELECT ' + @fname + ' from TableName '

Exec (@s)--Success

EXEC sp_executesql @s--this sentence is correct

3. Output parameters

declare @num int, @sql nvarchar (4000)

Set @sql = ' SELECT count (*) from TableName '

EXEC (@sql)
--How do I put the exec execution result into a variable?
declare @num int, @sql nvarchar (4000)

Set @sql = ' Select @a=count (*) from TableName '

EXEC sp_executesql @sql, N ' @a int output ', @num output Select @num

-----------------------

@maxnum int output--Maximum quantity

DECLARE @idcount INT--variables for storing EXEC statement assignments

declare @maxpagesql nvarchar (300)--Must be Nvarchaar

Set @maxpagesql = ' Select @idcount =count (ID) from questioninfo where 1=1 '

EXEC sp_executesql @maxpagesql, N ' @idcount int output ', @maxnum output

Select @maxnum

Dynamic SQL EXEC (http://www.cnblogs.com/Junelee1211/archive/2011/08/25/2153024.html)

1. Only one string variable is allowed in the parentheses of the EXEC command, or a string literal, or a string variable is concatenated with the string literal. You cannot use a function or case expression in parentheses, such as the following attempt to invoke the QuoteName function in parentheses to refer to the object name, the run will fail:

   1:  DECLARE @schemaname NVARCHAR (255), @tablename NVARCHAR (+)

   2:  SET @schemaname =' dbo ' 

   3:  SET @tablename =' Order Details ' 

   4:  

   5:  EXEC (N' SELECT COUNT (*) from ' +quotename (@schemaname) +n'. ' +quotename (@tablename) +n'; ')   

The code above will produce the following error:

There is a syntax error near message 102, Level 15, State 1, line 5th ' QUOTENAME '. SQL Server parse and compile time: CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

SQL Server Execution Time: CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

So the best way to do this is to construct the code into a variable so that it is not restricted, and then use that variable as an input parameter to the EXEC command, like this:

   1:  DECLARE @schemaname NVARCHAR (255),

   2:      @tablename NVARCHAR (128),

   3:      @SQL NVARCHAR (MAX) 

   4:  ' dbo '

   5:  ' Order Details '

   6:  SET @sql = N' SELECT COUNT (*) from ' + QUOTENAME (@schemaname) + N'. '   

   7:      + QUOTENAME (@tablename) + N'; '

   8:  EXEC (@sql) 

2. Exec does not provide an interface. EXEC (<string>) does not provide an interface. Its only input is the string containing the code you want to invoke. Dynamic batching cannot access local variables that are defined in the call batch. The following code attempts to access the variable defined in the call batch will fail.

   1:  

   2:  SET @i = 10248

   3:  

   4:  DECLARE @SQL NVARCHAR (MAX)  

   5:  

   6:  SET @' SELECT * FROM dbo. Orders WHERE [email protected]; ' 

   7:  EXEC (@sql) 

The following error will be generated:

MSG 137, Level 15, State 2, line 1th must declare the scalar variable "@i".

When using exec, if you want to access variables, you must concatenate the variable contents into the dynamically constructed code string.

declare @i int SET @i = 10248declare @sql NVARCHAR (max) set @ sql =  ' SELECT * FROM dbo. Orders WHERE orderid= ' + cast (@i as NVARCHAR (+)) +  '; ' exec (@sql)           

There's no problem with that.

If a variable contains a string, concatenating the contents of the variable into the code will result in a security risk (SQL injection), and in order to avoid SQL injection, you can limit the string size to the minimum required length. Of course, in practice this situation does not require dynamic SQL to execute SQL statements directly, this example is just for demonstration.

There are performance drawbacks to the contents of a concatenated variable, and SQL Server will create a new ad hoc execution plan for each unique query string, even if the query pattern is the same. To demonstrate this, first empty the execution plan in the cache.

DBCC Freeproccache

Execute the upper code three times, @i assigns 10248,10249 and 10250, and then use the following code to query

   1:  SELECT  cacheobjtype,

   2:          ObjType,

   3:          usecounts,

   4:          SQL

   5: From      sys.syscacheobjects

   6:  WHERE   '%cache% ' 

   7:          '%sys.% '

Get query results:

Cacheobjtype objtype usecounts SQL Compiled Plan adhoc 1 SELECT * FROM dbo.    Orders WHERE orderid=10250; Compiled Plan adhoc 1 SELECT * FROM dbo.    Orders WHERE orderid=10248; Compiled Plan Prepared 3 (@1 smallint) SELECT * FROM [dbo]. [Orders] WHERE [orderid][email protected] Compiled Plan adhoc 4 set STATISTICS IO on set STATISTICS time on Compiled Plan adhoc 1 SELECT * FROM dbo.    Orders WHERE orderid=10249; Compiled Plan adhoc 4 set STATISTICS IO off set STATISTICS time off

exec does not support output parameters in addition to input parameters in dynamic batching. By default, EXEC returns the query output to the caller. If you want to return the output to a variable in the call batch, it's not that simple, so you need to insert the output into a destination table using insert exec and then take the value from that table and assign it to that variable, like this:

   1:  DECLARE @schemaname NVARCHAR (+),

   2:      @tablename NVARCHAR (128),

   3:      @colname NVARCHAR (128),

   4:      @SQL NVARCHAR (MAX), 

   5:      INT

   6:      

   7:  ' dbo '

   8:  ' Orders '

   9:  ' CustomerID '

  10:       

  One:  SET @sql = N') from '  

  :      + QUOTENAME (@schemaname) + N'. ' + QUOTENAME (@tablename) + n'; ' 

  13:      

  Int.:  INT)

  :  INSERT into  #T1

  :          EXEC (@SQL 

  :              )

  :  SELECT  @cnt = cnt

  : From      #T1

  :  SELECT  @cnt

  :  TABLE #T1

3. When concatenating variable values in SQL Server2000, Exec has an advantage over sp_executesql, which supports longer code, although the sp_executesql input code string is ntext type. But you typically construct a code string in a local variable. You cannot declare a local variable with a large object type, so the query string that is actually executed in sp_executesql is limited to a maximum length of 4000 that is supported by the Unicode string (NVARCHAR). While Exec supports regular string (VARCHAR), the maximum of 8,000 characters is allowed. In addition, Exec supports a special feature that allows you to concatenate multiple variables in parentheses, each supporting a length of 8,000 characters.

In SQL Server2005, you don't have to be so tangled up, because you can provide a varchar (max) or nvarchar (max) variable as input for the EXEC command, and the input string can be up to 2GB in size

The difference between basic syntax--exec and EXEC sp_executesql for dynamic SQL statements