sp_executesql Stored Procedure Examples, Output and Return Values


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.

Let us use a simple example.

-- First create the temp table we will use


CREATE TABLE #tmp (id int)


-- 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

1 Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.