SQL Server and Dynamic SQL

Published 20/08/2005 by Henry in SQL
Tags:
HTML Source EditorWord wrap

'EXEC()', 'sp_executesql' and 'EXEC('EXEC sp_executesql ...')'

There are two ways to invoke dynamic SQL in MS SQL Server, 'EXEC()' and 'sp_executesql'. And you can use a combination of the two 'EXEC('EXEC sp_executesql ...')'.
EXEC() is the most simple, as illustrated by this example:

   1:  SELECT @table = 'sales' EXEC('SELECT * FROM ' + @table) 

The permissions of the current user always apply, even if the statement appears in a stored procedure. 'EXEC(''')' is similar to the EXEC of a stored procedure. But rather than calling a stored procedure, you invoke a batch of single SQL statements.

sp_executesql has the advantage that it allows you to pass parameters to the dynamic query, both for input and output.
Here is an example with an output parameter:

   1:  DECLARE @sql nvarchar(4000) -- nvarchar(MAX) on SQL 2005. DECLARE @col sysname 
   2:  DECLARE @min varchar(20) 
   3:  SELECT @col = N'au_fname' 
   4:  SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col + N')) FROM authors' 
   5:  EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT SELECT @min 

Thus, you can much easier get a value from your dynamic SQL statement into a local variable than with EXEC().

These points apply to EXEC() and sp_executesql as well:

  • The SQL code is it in own scope, and you have no access to the variables in the calling stored procedure;
  • The permissions of the actual user apply;
  • Any USE statement will not affect the calling stored procedure;
  • Temp tables created in the SQL batch will not be available to the calling procedure;
  • SET statements only affects the SQL batch, not the caller;
  • Termination of the batch executed by sp_executesql terminates the calling procedure too;
  • @@error reports the status of the last statement in the dynamic SQL code;
  • According to Books Online the return value from sp_executesql is either 0 for success or 1 for failure. However it appears that the return value is the final value of @@error, at least in SQL 2000.

EXEC('EXEC sp_executesql ....
By using 'EXEC('EXEC sp_executesql ....' it is possible to use statement parameters with the dynamic query string. The sql query must be in unicode string , in nvarchar the MAX characters is 4000. By using EXEC('EXEC sp_executesql ...., multiple parameters can be concatenated, which results in the possibility to use more than 4000 characters in the dynamic query (the point with dynamic ofcourse is that you can not be sure what length the query will be).

Example:

   1:  DECLARE @query nvarchar(4000)
   2:  DECLARE @@queryDynamic nvarchar(4000)
   3:  DECLARE @l_Startdate datetimeDECLARE @l_Enddate datetimeDECLARE @dbName varchar(50)
   4:  DECLARE @dbName2 varchar(50)
   5:   
   6:  SET @dbName = '[SQLServer_instance].[Databasename]'
   7:  SET @dbName2 = '[SQLServer_instance2].[Databasename2]' 
   8:  SET @query = 'SELECT * FROM ' + @dbName + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate '
   9:  SET @@queryDynamic = 'UNION ' + 'SELECT * FROM ' + @dbName2 + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate ' 
  10:   
  11:  EXEC('EXEC sp_executesql N''' + @query + @queryDynamic + ''', N''@pl_Startdate datetime ,
 12:                              @pl_Enddate datetime'', @pl_Startdate = ''' + @l_Startdate + ''', @pl_Enddate = ''' + @l_Enddate + '''')


Henry Cordes
My thoughts exactly...

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading