Exec vs sp_execute SQL

Code from Philly.NET code camp 2009.2 Saturday October 17, 2009

There are two large code blocks so don’t forget to scroll down.  The only reason for two is the JavaScript code formatter choked when it was too long.

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string dynamically
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

    -------------------------------------------------------------------------------
    -- Identical functionality executing a string
    -------------------------------------------------------------------------------
    -- Exec and Execute are interchangeable
    EXEC    (N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109')
    EXECUTE (N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109')

    EXECUTE sp_executesql N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109'

    -- Try removing the ( ) in the 1st statement - It doesn't work
    -- EXECUTE N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = 109'


    -------------------------------------------------------------------------------
    -- Not Identical functionality executing a string
    -------------------------------------------------------------------------------
    --Execute supports building the sting dynamically as part of the execution

    EXECUTE (N'SELECT * FROM AdventureWorks.HumanResources.Employee ' 
    + 'WHERE ManagerID = 109')

    EXECUTE sp_executesql N'SELECT * FROM AdventureWorks.HumanResources.Employee ' 
    + 'WHERE ManagerID = 109'
    -- This doesn't work because the text string is an invalid as a parameter to a SP


GO 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string dynamically
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @SelectSQL        VARCHAR(Max)
DECLARE @WhereSQL        VARCHAR(Max)
DECLARE @SQLStatement    NVARCHAR(Max) -- required by EXECUTE sp_executesql 
DECLARE @ManagerID        TINYINT 

SET @ManagerID = 109

SET @SelectSQL = 'SELECT * FROM AdventureWorks.HumanResources.Employee ' --<<---Notice Space at end
SET @WhereSQL =  'WHERE ManagerID = ' + CAST(@ManagerID AS VARCHAR(10))
SET @SQLStatement = @SelectSQL + @WhereSQL

SELECT @SelectSQL, @WhereSQL, @ManagerID, @SQLStatement


EXEC (@SelectSQL + @WhereSQL) -- Must use or () or the command is a totally different thing
EXEC (@SQLStatement)

EXECUTE sp_executesql @SQLStatement

-- These don't work
--EXEC @SelectSQL + @WhereSQL -- Must use or () or the command is a totally different thing
--EXEC @SQLStatement

-- These don't work either since ( ) not allowed in Sproc execution
--EXECUTE sp_executesql (@SelectSQL + @WhereSQL)
--EXECUTE sp_executesql (@SQLStatement)



GO 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string dynamically with parameter
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXECUTE sp_executesql 
    N'SELECT * FROM AdventureWorks.HumanResources.Employee 
    WHERE ManagerID = @ManagerID',
    N'@ManagerID tinyint',
    @ManagerID = 109;

-- Use of explicitly named parameters
-- Below the line is dynamic in number of parameters accepted
EXECUTE sp_executesql 
      @stmt = N'SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = @ManagerID'
    , @params = N'@ManagerID tinyint'
    --------------------------------
    , @ManagerID = 109; -- Good to use ;


GO 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Executing a string Variable dynamically with parameter
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @SelectSQL        VARCHAR(Max)
DECLARE @WhereSQL        VARCHAR(Max)
DECLARE @SQLStatement    NVARCHAR(Max) -- required by EXECUTE sp_executesql 
DECLARE @ManagerID        TINYINT 

SET @ManagerID = 109

SET @SelectSQL = 'SELECT * FROM AdventureWorks.HumanResources.Employee ' --<<---Notice Space at end
SET @WhereSQL =  'WHERE ManagerID = @ManagerID' --<<-- Notice use of Variable in string
SET @SQLStatement = @SelectSQL + @WhereSQL

SELECT @SelectSQL, @WhereSQL, @ManagerID, @SQLStatement


EXECUTE sp_executesql 
      @stmt = @SQLStatement
    , @params = N'@ManagerID tinyint'
    --------------------------------
    , @ManagerID = @ManagerID; 
    

-- Change the value of the parameter and execute again
-- Execution plan cached and parameterized
SET @ManagerID = 108

EXECUTE sp_executesql 
      @stmt = @SQLStatement
    , @params = N'@ManagerID tinyint'
    --------------------------------
    , @ManagerID = @ManagerID; 
    
    

GO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Parameterized SQL with output variables
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @ManagerID        TINYINT 
DECLARE @NumRecs        INT

SET @ManagerID = 109


EXECUTE sys.sp_executesql 
      @stmt = N'SELECT @Count = count(*) FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = @ManagerID'
    , @params = N'
          @Count    int output -- output need here too
        , @ManagerID    tinyint
        '
    , @Count    = @NumRecs OUTPUT  -- Don't for get output
    , @ManagerID = @ManagerID
    ;    

SELECT @NumRecs AS [@NumRecs], @ManagerID AS [@ManagerID]

-- did you notice the comment in the @params section

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Interesting variations
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- sp_tables [ [ @table_name = ] 'name' ] 
--    [ , [ @table_owner = ] 'owner' ] 
--    [ , [ @table_qualifier = ] 'qualifier' ] 
--    [ , [ @table_type = ] "type" ] 
--    [ , [@fUsePattern = ] 'fUsePattern'];
 
---------------------------------------
-- No Params since they are all optional
---------------------------------------
EXEC sys.sp_tables
;

---------------------------------------
-- Position aligned params
---------------------------------------
EXEC sys.sp_tables NULL,NULL,NULL,NULL,NULL
;
EXEC sys.sp_tables NULL,'HumanResources',NULL,NULL,NULL
;


---------------------------------------
-- named params
---------------------------------------
EXEC sys.sp_tables       
      @table_name = NULL
    , @table_owner = NULL
    , @table_qualifier = NULL
    , @table_type = NULL
    , @fUsePattern = NULL 
;

EXEC sys.sp_tables
      @table_name = DEFAULT --<<-- Notice use of default
    , @table_owner = 'Sales'
    , @table_qualifier = DEFAULT
    , @table_type = "'VIEW','TABLE'"
    , @fUsePattern = DEFAULT 
;

---------------------------------------
-- Can change the order of named params
---------------------------------------
EXEC sys.sp_tables
      @table_owner = 'Sales'
    , @table_type = "'VIEW'"
    , @table_qualifier = NULL
    , @fUsePattern = NULL 
    , @table_name = DEFAULT
;    

---------------------------------------
-- can omit params with a default val
---------------------------------------
EXEC sys.sp_tables
      @table_type = "'VIEW'"
    , @table_owner = 'Sales'
;


go

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- And now for something compeletely Different
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

DECLARE @SprocName    VARCHAR(max)
DECLARE @Owner        VARCHAR(64)

SET @SprocName  = 'sys.sp_tables'
SET @Owner        = 'Sales'

EXEC @SprocName                    --<<-- Notice the lack of ( ) 
      @table_owner = @Owner
    , @table_type = "'VIEW'"

-- This is an example of dynmically executing a stored procedure with Exec syntax