[FIXED] How to store result of EXEC sys.sp_executesql

Issue

How can I store the result of exec into a variable?

Here's my attempt at storing the data in a variable:

DECLARE @oltp VARCHAR(50) SET @oltp = 'emp'
DECLARE @wh VARCHAR(25) SET @wh = '_wh'
DECLARE @DatabaseName SYSNAME = @oltp + @wh;
DECLARE @oltp_data_days NVARCHAR(500)
DECLARE @Params NVARCHAR(500)
DECLARE @vi int

SET @oltp_data_days = N'(SELECT datediff(DAY, MIN(business_date), GETDATE()) AS keep_days
        FROM '+QUOTENAME(@DatabaseName)+'..sales 
        WHERE business_date > ''1/1/2000 00:00:00'')'

EXEC sp_executesql  @oltp_data_days  = @oltp_data_days
      , @Params = N'@vi INT OUTPUT'
      , @vi = @vi OUTPUT

I am getting results as Null.

Solution

Like this:

declare @sql nvarchar(max) = N'set @vi = (select 3 as keep_days)'
declare @vi int

exec sp_executesql @sql, N'@vi int output', @vi = @vi output

select @vi 

Answered By - David Browne - Microsoft

Answer Checked By - Clifford M. (PHPFixing Volunteer)