Null value is eliminated by an aggregate or other SET operati

[Azita79]

Hi All,

when I execute my sproc, passing a null value, I get this error:

Warning: Null value is eliminated by an aggregate or other SET operation.

here is the sproc

Create procedure dbo.Search
(@BeginDate DateTime,
@EndDate DateTime)
As

DECLARE @vBeginDate NVARCHAR(20)
DECLARE @vEndDate NVARCHAR(20)
DECLARE @vStrSQL NVARCHAR(2000)

IF @BeginDate IS NOT NULL OR @EndDate IS NOT NULL
BEGIN
IF @BeginDate IS NULL
SELECT @BeginDate = MIN(A.ApprecdDate)
FROM WQOnsite.dbo.tblApplication A

IF @EndDate IS NULL
SELECT @EndDate = MAX(A.ApprecdDate)
FROM WQOnsite.dbo.tblApplication A

SET @vBeginDate = convert(VARCHAR(12), @begindate, 102)
SET @vEndDate = convert(VARCHAR(12), @EndDate, 102)
SET @vstrsql = 'SELECT * FROM tblApplication A where a.appRecddate between ''' +
@vBeginDate + ''' and ''' + @vEndDate + ''''

END

exec (@vStrSQL)

***********************

(EXEC SEARCH '1/1/1992', NULL )gives me the right result but I get the above warnning and the same with begindate is being null.


Thank you for your help in advance

 

[tlbroadbent]

The message is simply a warning. If you want to eliminate it, use the following line at the beginning of the procedure.

SET ANSI_WARNINGS OFF If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Azita79]

Thanks for the hint Terry, but still getting the same warning.

Any thoughts anyone???


Azita

 

[tlbroadbent]

Did you add the SET statement in the procedure after the AS and before the IF statements? It WILL eliminate the messages if used properly. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Azita79]

Terry,

Now that I put it after the AS I don't get the warning message, but I'm still getting an error message when I execute it from the fortend(Access Unbound Form)

"The object you entered is not a valid RecordSet porperty"

I get the above error when either of the dates are NULL.

 

[tlbroadbent]

You should now post your Access code in the Access Modules forum so someone can help you resolve the Access error.
If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Azita79]

Thanks Terry, I will

Azita