The following code works perfectly when run by itself:
DECLARE @.DateTransaction smalldatetime
DECLARE @.TradeDate smalldatetime
SET @.DateTransaction = (SELECT Max(DateTransaction) FROM tblImport)
SET @.TradeDate = (SELECT Max(DateTrade) FROM tblSystem)
PRINT 'The Transaction Date is ' + CAST(@.DateTransaction as varchar) + '. The Trade Date is ' + CAST(@.TradeDate as varchar)
However, it is part of a long stored procedure, and when it runs there, I get the following instead of the line confirming the two dates:
Warning: Null value is eliminated by an aggregate or other SET operation.
What could be interfering with this section of the stored procedure?
Further research has detected that the following code:
DECLARE @.DateTransaction smalldatetime
SET @.DateTransaction = (SELECT TOP 1 (DateTransaction) FROM tblImport ORDER BY DateTransaction DESC)
although the following part isolated returns the correct value:
DECLARE @.DateTransaction smalldatetime
SET @.DateTransaction = (SELECT TOP 1 (DateTransaction) FROM tblImport ORDER BY DateTransaction DESC)
Why is the variable not being set?
WHEN THE FOLLOWING CODE IS RUN IN ISOLATION:
DECLARE @.DateTransaction smalldatetime
DECLARE @.TradeDate smalldatetime
SET @.DateTransaction = (SELECT Max(DateTransaction) FROM tblImport)
SET @.TradeDate = (SELECT Max(DateTrade) FROM tblSystem)
PRINT 'The Transaction Date is ' + CAST(@.DateTransaction as varchar) + '. The Trade Date is ' + CAST(@.TradeDate as varchar)
IT RESULTS IN THIS:
The Transaction Date is May 15 2006 12:00AM. The Trade Date is May 15 2006 12:00AM
WHEN THE CODE IS RUN AS PART OF A LONGER STORED PROCEDURE, IT RESULTS IN THIS:
The Transaction Date is null. The Trade Date is May 15 2006 12:00AM
WHAT IS HAPPENING?
|||I added an update earlier in the stored procedure and it solved the problem.
Thanks for your help.
