How to store Exec result to datetime variable where exec result has variable table name?-CodePudding

How can I store my execution result in datetime variable?

My query look like this:

Declare @F VARCHAR(50) = (select replace ('H-10','-',''));
DECLARE @SQLQuery AS NVARCHAR(500)
set @SQLQuery=
N'SELECT
   top 1 DATEADD(MINUTE, -330, time_stamp) as time
 FROM
  DMD_' @F '_DC_data
 ORDER BY
  time_stamp ASC';
DECLARE @SOR_time datetime
   
set @SOR_time=Exec (@SQLQuery)

CodePudding user response：

Try following way. Execute query result store into the table instead of assign to a variable.

Declare @F VARCHAR(50) = (select replace ('H-10','-',''));
DECLARE @SQLQuery AS NVARCHAR(500)
set @SQLQuery=
N'SELECT
   top 1 DATEADD(MINUTE, -330, time_stamp) as time
 FROM
  DMD_' @F '_DC_data
 ORDER BY
  time_stamp ASC';

DECLARE @TempTable TABLE (SOR_time datetime)
insert @TempTable 
exec (@SQLQuery)
select * from @TempTable

CodePudding user response：

You can just assign to variable inside query if result set is just a single variable

Declare @F VARCHAR(50) = (select replace ('H-10','-',''));
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @SOR_time DATETIME

set @SQLQuery=
N'SELECT
   top 1 @SOR_time = DATEADD(MINUTE, -330, time_stamp) as time
 FROM
  DMD_' @F '_DC_data
 ORDER BY
  time_stamp ASC';

-- PRINT @SQLQuery  

Exec (@SQLQuery)
SELECT @SOR_time

CodePudding user response：

You need to use sp_executesql with an OUTPUT parameter:

DECLARE @F varchar(50) = (select replace ('H-10', '-', '')); 
DECLARE @SOR_time datetime  
DECLARE @SQLQuery AS nvarchar(500) 

SET @SQLQuery = 
   N'SELECT TOP 1 @SOR_time = DATEADD(MINUTE, -330, time_stamp) '  
   N'FROM QUOTE_NAME(DMD_'   @F   '_DC_data) '  
   N'ORDER BY time_stamp ASC'
; 

DECLARE @rc int
EXEC @rc = sp_executesql 
   @SQLQuery, 
   N'@SOR_time datetime OUTPUT', 
   @SOR_time OUTPUT
IF @rc <> 0 PRINT 'Error'