stored proc error

Question

Hi there,

Anyone pls suggest why I am getting an error like below:

My Sp script is like below :

USE DB

Create PROCEDURE [dbo].[sp_MakeFact] AS

SET NOCOUNT ON;

DROP TABLE IF EXISTS AmberCare;

WITH AmberCare AS (
select * from table)

SELECT
AmberCare.[FACILITY]
,AmberCare.[MRN]
,AmberCare.[AUID]
,1 as Measure_AmberCare
,0 as Measure_Advancecare
INTO final_table
FROM AmberCare
LEFT JOIN
[APU_BI].[dbo].[VIEW_EPISODE_MASTER] EM
ON EM.stay_number = AmberCare.visit_id
AND EM.facility_identifier = AmberCare.Facility_identifier;

DROP TABLE IF EXISTS Advancecare;

WITH AmberCare AS (
select * from table)

SELECT
AdvanceCare.[FACILITY]
,AdvanceCare.[MRN]
,AdvanceCare.[AUID]
,0 as Measure_Ambercare
,1 as Measure_Advancecare
INTO final_table
FROM Advancecare
LEFT JOIN
[VIEW] EM
ON EM.stay_number = Advancecare.visit_id
AND EM.facility_identifier = Advancecare.Facility_identifier
AND ADVANCE_CARE_DIRECTIVE='YES';

when exec the sp I am getting below error:

Answer 1

Hi @ASHMITP ,

Welcome to Microsoft Q&A!

Please also provide the details of [VIEW] which may cause this warning. There could be one aggregation on one column which has null values in your [VIEW].

We could disable this warning by setting ansi_warnings off but this may cause other effects or cause failures when your queries use features like indexed views, computed columns or XML methods.

In some cases you could rewrite the aggregate to avoid it.

For example, COUNT(nullable_column) could be rewritten as SUM(CASE WHEN nullable_column IS NULL THEN 0 ELSE 1 END) or SUM(IIF(nullable_column IS NULL,0 ,1) but this isn't always possible to do straightforwardly without changing the semantics.

You could refer below example:

select count(id) from (  
select 1 id  
union  
select NULL) a  

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

We could rewrite it as below:

select sum(IIF(id is null,0,1)) from (  
select 1 id  
union  
select NULL) a  

Output:
1

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As the message indicates, this is a warning, not an error. What it is telling you is that this query as one or more aggregate function (for example COUNT() or SUM()) and one or more of the values being aggregated is NULL. When that happens, the NULL values are not included in the aggregate. For example, if you do

Declare @T Table(i int);
Insert @T(i) Values (1), (Null), (3);
Select COUNT(i) As TheCount, SUM(i) As TheSum From @T;

the COUNT will show as 2, the SUM as 4 and you will get the warning message because the NULL value is not included in either the COUNT or the SUM.

But if you do

Declare @T Table(i int);
Insert @T(i) Values (1), (Null), (3);
Select COUNT(IsNull(i, 0)) As TheCount, SUM(IsNull(i, 0)) As TheSum From @T;

Then the COUNT will be 3 and the sum will be 4 and you will not get the warning message.

Tom

Answer 3

This is not an error message, but an informational message mandated by the ANSI standard. Treat it as white noise. Occasionally, you can get rid of it by adjusting the query, but most of the time it is not worth the effort.

And most emphatically, never use SET ANSI_WARNINGS OFF!