October 14, 2016 at 3:17 am
Dear all,
I have this tored procedure:
ALTER PROCEDURE [bmb_ms].[usp_BMBBI_audit_procedure_result_update]
@datSSISStartDate DATETIME,
@datSSISEndDate DATETIME,
@intMarketCodeINT,
@intNumRead INT OUTPUT,
@intNumInserted INT OUTPUT,
@intNumUpdated INT OUTPUT,
@intNumFailed INT OUTPUT
AS
BEGIN
--The transaction is handled by the caller SSIS package
--========================================================================
--PROCEDURE: usp_BMBBI_audit_procedure_result_update
--SYSTEM: BMB-V5 Nestlé
--DESCRIPTION: Update audit_procedure_result.perf_index
--AFFECTED TABLE(S) :dbo.audit_procedure_result
--PARAMETERS : @strMarketList -- String containing the list of market to process
-- @datSSISStartDate - Begining date of the extraction time window
-- @datSSISEndDate - Ending date of the extraction time window
--COPY ON: BMB_marketV5
--
--========================================================================
--SUMMARY OF CHANGES
-- DATE(dd-mm-yyyy)AUTHOR COMMENT
-- 13-07-2009Zoltan Szep Created
--========================================================================
SET NOCOUNT ON;
-- Error handling
DECLARE
@strErrorMessageVARCHAR(4000),
@intErrorSeverityINT,
@intErrorStateINT
BEGIN TRY
SET @intNumInserted = 0;
SET @intNumFailed = 0;
WITH AuditProcedureResultDelta AS
(
SELECT apr.audit_procedure_result_id
FROM dbo.audit_procedure_result apr
WHERE
EXISTS (
SELECT 1
FROM dbo.audit_procedure_kpi_result sub_apkr
WHERE
sub_apkr.audit_procedure_result_id = apr.audit_procedure_result_id
AND
sub_apkr.modf_on BETWEEN @datSSISStartDate AND @datSSISEndDate
)
AND apr.market_code = @intMarketCode
)
UPDATE dbo.audit_procedure_result
SET perf_index = (
SELECT CASE
WHEN ISNULL(SUM(K.[weight]),0) = 0 THEN 0
ELSE ISNULL(SUM(CAST(kr.index_score as decimal(12,2))) ,0) / SUM(K.[weight]) * 100.0
END AS perf_index
FROM dbo.audit_procedure_kpi_result apkr
INNER JOIN dbo.kpi_response kr
ON kr.kpi_id = apkr.kpi_id
AND kr.kpi_response_id = apkr.kpi_response_id
INNER JOIN dbo.KPI k
ON kr.kpi_id = k.kpi_id
WHERE apkr.audit_procedure_result_id = AuditProcedureResultDelta.audit_procedure_result_id
GROUP BY apkr.audit_procedure_result_id
)
FROM AuditProcedureResultDelta
WHERE
dbo.audit_procedure_result.audit_procedure_result_id = AuditProcedureResultDelta.audit_procedure_result_id
SET @intNumUpdated = @@ROWCOUNT
SET @intNumRead = @intNumUpdated
END TRY
BEGIN CATCH
SET @intNumFailed = @@ROWCOUNT
SELECT
@strErrorMessage = ERROR_MESSAGE(),
@intErrorState = ERROR_STATE(),
@intErrorSeverity = ERROR_SEVERITY()
RAISERROR (
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
-- Standard failure END
END CATCH
END
if I execute it like this:
exec [bmb_ms].[usp_BMBBI_audit_procedure_result_update] '2016-06-02 00:00:00.000','2016-06-02 23:59:59.999', 2009,
@intNumRead = @intNumRead OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED OUTPUT, @INTNUMFAILED = @INTNUMFAILED OUTPUT
I get the following answer:
Warning: Null value is eliminated by an aggregate or other SET operation.
But if I execute it from the GUI (right click in the stored proc and execute, it executes right. Can you please help understand?
Thanks
October 14, 2016 at 3:23 am
Quick guess, the GUI has SET ANSI_WARNINGS OFF while SSMS has SET ANSI_WARNINGS ON
😎
October 14, 2016 at 5:08 am
That's not an error, it's a warning that nulls are being eliminated due to aggregates.
Depending on the requirements, that may be a warning you can ignore, or an indication that there's a problem with the data. It's not going to prevent the procedure from running though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply