Help with dynamic SQL & SET stmt

[katbear]

Hi,

I want to set the result of EXEC(@SQL) to a variable. That is, I want the MAX(ProductionDate) to be stored in a variable called @MaxTradeDate.

It has to be dynamic because @myTable will be a parameter whose value will change.

SET @SQL =
'SELECT MAX(ProductionDate) FROM ' + @myTable

SET @MaxTradeDate = EXEC(@SQL)

I get this error:
Msg 156, Level 15, State 1, Procedure myProc, Line 9
Incorrect syntax near the keyword 'SET'.

Thanks much

 

[bborissov]

DECLARE @MaxTradeDate datetime
SET @SQL =
  'SELECT @MaxTradeDate = MAX(ProductionDate) FROM ' + @myTable

sp_executeSQL @SQL, N'@MaxTradeDate datetime OUTPUT', @MaxTradeDate OUTPUT

SELECT @MaxTradeDate

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[katbear]

Getting this error:

Msg 102, Level 15, State 1, Procedure myProc, Line 15
Incorrect syntax near 'SP_EXECUTESQL'.

 

[roccorocks]

DECLARE @MyTable VARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @MaxTradeDate datetime
SET @SQL = N'SELECT @MaxTradeDate = MAX(StartDate) FROM ' + @myTable
EXECUTE sp_executeSQL @SQL , N'@MaxTradeDate datetime OUTPUT', @MaxTradeDate OUTPUT
SELECT @MaxTradeDate

bborissov pretty much had it. Just be aware that SQL requires unicode for doing these queries (the N items).

Thanks,

Rocco

 

[katbear]

Thanks for the examples. My solution was a little different:

SET @SQL = N'SELECT @MaxTradeDateOUT = MAX(DATEADD(dd, DATEDIFF(dd, 0, ProductionDate), 0)) FROM ' + @myTable

SET @ParamDef = N'@MaxTradeDateOUT DATETIME OUTPUT'

EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @MaxTradeDateOUT = @MaxTradeDate OUTPUT

The difference being that "@MaxTradeDate OUTPUT" became "@MaxTradeDateOUT = @MaxTradeDate OUTPUT". This is what ended up compiling correctly, and producing the correct results.