sp_executesql

The sp_executesql SP is a system SP provided by Microsoft. This SP can be found in the master database. Here is the calling syntax for this SP:

sp_executesql [@stmt =] stmt
[ 
    {, [@params =] N'@parameter_name  data_type [,...n]' } 
    {, [@param1 =] 'value1' [,...n] }
]

Where [@stmt =] stmt is the dynamic T-SQL statement that you would like to execute, [@params =] N'@parameter_name  data_type [,...n]' is used to identify the list of variables and the data type for each dynamic variable that is contained in the dynamic T-SQL statement, and [@param1 =] 'value1' [,...n] is used to provide values for each variable used in the dynamic T-SQL statement being executed. More information about this stored procedure, and how to use it can be found in Microsoft SQL Server Books Online.
In order to show you how to return variables from the sp_executesql SP to your calling T-SQL, I will show you a real simple example. My example will return the numbers of records in a table that meet a specific WHERE criteria that is pass dynamically to the sp_executesql SP. Here is the code:

use Northwind
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + 
           ' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
                   N'@RECORDCNT int out, @ORDERID int', 
                   @RECCNT out,
                   @ORDID  
print 'The number of records that have an OrderId' +  
      ' greater than ' + @ORDID + ' is ' + 
      cast(@RECCNT as char(5))

In this example you can see that the table from which I am returning the record count is the "Orders" table in the "Northwind" database. To make this code dynamic, the WHERE statement criteria is built on the fly by using a value passed in to the sp_executesql SP call which will set the @ORDERID variable in the dynamic T-SQL string. The record count for the number of Orders records that have an OrderId less than the @ORDERID variable value will be returned in a variable named @RECORDCNT. Both these local variables are specified in the dynamic T-SQL command, which is passed to sp_executesql SP in the string @CMD. These variables are not declared the calling T-SQL code, but instead are declared within the sp_executesql SP.

Note that the above code has a couple of PRINT statements. The first PRINT statement in the code above displays the dynamic T-SQL command to be executed by the sp_executesql SP. Here is the dynamic T-SQL code that is displayed when this first PRINT statement is executed:

SELECT @RECORDCNT=count(*) from [Orders] where OrderId < @ORDERID

Since this T-SQL statement contains variables, the sp_executesql (SP) requires that the calling T-SQL code identify these variables, and their format in the parameters passed to this SP. Specifying the definition of these variables is done in the second parameter of the sp_executesql call above (N'@RECORDCNT int out, @ORDERID int'). As you can see the @RECORDCNT variable is defined as output. This allows this variable to return a value from the sp_executesql to the calling T-SQL code. Defining the definitions of these variables allows the sp_executesql SP to define these variables locally within the SP code.
When variables are used in the calling program to pass and return values you also need to identify these variables to sp_executesql SP. You need to do this so that the sp_executesql SP knows which variables in the calling program are associated with the variables placed in the command string, which is passed in the first parameter (@stmt) of the sp_executesql SP. In this example, I wanted to associate the variable @RECORDCNT with the variable @RECCNT, which is defined in my calling T-SQL code, as well as associate @ORDERID with the @ORDID variable, which is also defined in my calling T-SQL code. These variable associations are done using the final parameters passed to the sp_executesql SP; in my example this would be the third and forth parameters in the sp_executesql statement in the code above. The order of the third and forth parameters, in this case, need to match the order in which these variables are defined in the second parameter of the sp_executesql call. Also important is to identify that the @RECCNT variable is output. This allows the sp_executesql statement to set the @RECCNT variable with the results of @RECORDCNT when the sp_executesql statement is processed.
The output of the above script, when run on my server, displays the following output from the final PRINT statement in the script:

The number of records that have an OrderId greater than 10436 is 188

Conclusion

Although I have shown you only a simple example, which could have easily been done a number of other ways, hopefully you see the value of how the sp_executesql SP can process dynamic T-SQL and return results to variables in the calling T-SQL code. If you already have a need for this functionality then you should be able to expand on this method to meet your needs. As you can see, it is relatively straightforward to get your dynamic T-SQL code to set variables in your calling T-SQL when using the sp_executesql SP.

source : http://www.databasejournal.com/features/mssql/article.php/3286501/T-SQL-Programming-Part-4---Setting-Variables-in-Calling-T-SQL-Code-While-Using-spexecutesql.htm