stored proc error

ASHMITP 141 Reputation points
2021-05-21T05:28:07.01+00:00

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:

98428-cap.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

3 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-05-21T06:07:54.167+00:00

    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.

    0 comments No comments

  2. Tom Cooper 8,461 Reputation points
    2021-05-21T06:31:35.15+00:00

    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

    0 comments No comments

  3. Erland Sommarskog 98,911 Reputation points
    2021-05-21T22:18:08.133+00:00

    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!

    0 comments No comments