sp_executesql Stored Procedure Examples, Output and Return Values

sp_executesql

The sp_executesql command is ideally suited for those occasions where you need to dynamically create a SQL statement.

Embedded parameters can easily be added to the string that is created, which means the command is extremely versatile.

By creating dynamic SQL in your applications you can make a much more robust and scalable solution.

There is nothing worse than working on platforms that have hard-coded db names as part of the code!

sp_executesql vs exec

There are a couple of key differences between exec and sp_executesql.

  • Security – sp_executesql is generally considered more secure than exec
  • sp_executesql can use query plans that are cached, exec however, performs similar to adhoc queries (this can be good, or bad..)

sp_executesql Output (or Return Value) Example

It is possible to get output back from the sp_executesql statement, and the way to do this is to use the OUTPUT command alongside a variable name.

Lets use simple example.

--------------------------------------------
-- First create the temp table we will use

USE [DBAFire_DB]
GO

CREATE TABLE #tmp (id int)

INSERT INTO #tmp
SELECT 10
INSERT INTO #tmp
SELECT 5

-- now get the max id and return as output

DECLARE @retvalue int   
DECLARE @SQL nvarchar(50);
DECLARE @ParmDef nvarchar(50);


SELECT @SQL = N'SELECT @retvalOUT = MAX(ID) FROM #tmp' ;  
SET @ParmDef = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @SQL, @ParmDef, @retvalOUT=@retvalue OUTPUT;

SELECT @retvalue;
--------------------------------------------

sp_executesql Table Variable

You can only pass a table variable to the sp_executesql command if you use a named type. This article explains it well.

Hopefully this helps you, a quick and easy example of how to use the sp_executesql command with an OUTPUT.

Let us know if you get stuck!

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply