khan khan - 2 months ago 9
SQL Question

Insert into temptable from stored procedure having ouput variable

create table #TagsTable ([StandardDeviation] nvarchar,
[AffectedPing] nvarchar,
[AffectedPingPercentage] nvarchar,
[BaselineDataPointsCount] nvarchar)


DECLARE @IsAlert Bit
insert into #TagsTable
EXEC ProcessPingErrorAlert_SingleRow
@AlterId = 1,
@Hour = 1,
@Day = '2016-02-02',
@TraceId = 1583,
@IsAlert = @IsAlert OUTPUT


select * from #TagsTable


Error:


An INSERT EXEC statement cannot be nested


When I remove the output variable from the stored procedure, it works. How can I insert into temp table from stored procedure having output variable?

Procedure definition:

CREATE PROCEDURE [dbo].[ProcessPingErrorAlert_SingleRow] --1 ,12,'2015-12-08',1
(@AlterId INT,
@Hour INT,
@Day DATE,
@TraceId INT,
@IsAlert BIT OUTPUT)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @AlertType VARCHAR(250)
DECLARE @BaselineId INT
DECLARE @DatasetId INT
DECLARE @DatasetCount INT
DECLARE @AlertThresholdCount INT
DECLARE @PingErrorDatasetHourCount INT

IF NOT EXISTS(SELECT TOP 1 AlertId
FROM Alert
WHERE AlertId = @AlterId
AND ACTIVE = 1
AND IsDeleted = 0)
BEGIN
RAISERROR ('Alert not found.', 16, 1);
RETURN;
END

SELECT @AlertType = Code
FROM AlertType a
JOIN Alert b ON a.AlertTypeId = b.AlertTypeId
WHERE b.AlertId = @AlterId
AND a.Code = 'PingErrors'

IF @AlertType IS NULL
BEGIN
RAISERROR ('Invalid alert type.', 16, 1);
RETURN;
END

SELECT @BaselineId = a.BaselineId
FROM BaselineConfiguration a
JOIN BaselineType b ON a.BaselineTypeId = b.Id
JOIN AlertBaseline c ON a.BaselineId = c.BaselineId
WHERE b.Code = 'PingErrorHourly'
AND c.AlertId = @AlterId
AND a.Active = 1
AND a.IsDeleted = 0

IF @BaselineId IS NULL
BEGIN
RAISERROR ('Baseline not found.', 16, 1);
RETURN;
END

SELECT @DatasetId = Id
FROM BaselineDatasetSetting
WHERE BaseLineId = @BaselineId
AND IsCalculated = 1

IF @DatasetId IS NULL
BEGIN
RAISERROR ('Baseline dataset not found.', 16, 1);
RETURN;
END

IF OBJECT_ID('tempdb.dbo.#PingErrorDataset') IS NOT NULL
BEGIN
DROP TABLE #PingErrorDataset
END;
CREATE TABLE #PingErrorDataset
(
[BaselineId] [int] NOT NULL,
[Day] [datetime] NOT NULL,
[Hour] [tinyint] NOT NULL,
[PingCount] [int] NOT NULL,
[ErrorCount] [int] NOT NULL,
[ErrorRatio] [float] NOT NULL,
[RefreshLogId] [int] NOT NULL
)
INSERT INTO [dbo].[#PingErrorDataset]
(
[BaselineId],
[Day],
[Hour],
[PingCount],
[ErrorCount],
[ErrorRatio],
[RefreshLogId]
)
SELECT ped.[BaselineId],
ped.[Day],
ped.[Hour],
ped.[PingCount],
ped.[ErrorCount],
ped.[ErrorRatio],
ped.[RefreshLogId]
FROM [dbo].[PingErrorDataset] ped
WHERE ped.[BaselineId] = @BaselineId
AND ped.[Day] < @Day
AND ped.[Hour] = @Hour

SELECT @DatasetCount = COUNT(*)
FROM [#PingErrorDataset]

IF @DatasetCount IS NULL
OR @DatasetCount = 0
BEGIN
RAISERROR ('No data found in the baseline dataset.', 16, 1);
RETURN;
END

IF OBJECT_ID('tempdb.dbo.#AlertThreshold') IS NOT NULL
BEGIN
DROP TABLE #AlertThreshold
END;
CREATE TABLE #AlertThreshold
(
[AlertId] [int] NOT NULL,
[ThresholdId] [int] NOT NULL,
[ThresholdValue] [varchar](250) NOT NULL,
[OperatorId] [int] NOT NULL,
[ThresholdType] VARCHAR(250) NOT NULL,
[ThresholdCode] VARCHAR(250) NOT NULL,
[Operator] VARCHAR(50) NOT NULL
)

INSERT INTO #AlertThreshold
(
[AlertId],
[ThresholdId],
[ThresholdValue],
[OperatorId],
[ThresholdType],
[ThresholdCode],
[Operator]
)
SELECT a.AlertId,
a.ThresholdId,
a.ThresholdValue,
a.OperatorId,
b.ThresholdType,
b.Code,
c.Operator
FROM AlertThreshold a
JOIN Threshold b
ON a.ThresholdId = b.ThresholdId
JOIN ComparisonOperator c
ON a.OperatorId = c.OperatorId
WHERE a.AlertId = @AlterId

SELECT @AlertThresholdCount = COUNT(*)
FROM [#AlertThreshold]

IF @AlertThresholdCount IS NULL
OR @AlertThresholdCount = 0
BEGIN
RAISERROR ('No Threshold found for the alert.', 16, 1);
RETURN;
END

IF OBJECT_ID('tempdb.dbo.#PingErrorDatasetHour') IS NOT NULL
BEGIN
DROP TABLE #PingErrorDatasetHour
END;
CREATE TABLE #PingErrorDatasetHour
(
[BaselineId] [int] NOT NULL,
[Day] [datetime] NOT NULL,
[Hour] [tinyint] NOT NULL,
[PingCount] [int] NOT NULL,
[ErrorCount] [int] NOT NULL,
[ErrorRatio] [float] NOT NULL
)

INSERT INTO [dbo].[#PingErrorDatasetHour]
(
[BaselineId],
[Day],
[Hour],
[PingCount],
[ErrorCount],
[ErrorRatio]
)
EXEC [CalculatePingErrorRatio] @BaselineId,
@Day,
@Hour

SELECT @PingErrorDatasetHourCount = COUNT(*)
FROM [#PingErrorDatasetHour]

IF @PingErrorDatasetHourCount IS NULL
OR @PingErrorDatasetHourCount = 0
BEGIN
RAISERROR ('No data found for the hour specified.', 16, 1);
RETURN;
END


DECLARE @ErrorRatio DECIMAL
DECLARE @AveragePingErrorRatio DECIMAL
DECLARE @Query NVARCHAR(4000) = ''
DECLARE @PingErrorRatioStandardDeviation FLOAT
DECLARE @SDThresholdValue INT
DECLARE @PingErrorRatioSDValue DECIMAL
DECLARE @SDOperator VARCHAR(50)

DECLARE @APThresholdValue INT
DECLARE @APOperator VARCHAR(50)
DECLARE @AffectedPings INT

DECLARE @APPThresholdValue INT
DECLARE @APPOperator VARCHAR(50)
-- Getting actual values of PingCount, PingErrorRatio, AffectedPings and ServiceName
SELECT @ErrorRatio = ErrorRatio,
@AffectedPings = ErrorCount
FROM [#PingErrorDatasetHour]
WHERE [Day] = @Day
AND [Hour] = @Hour

--Getting Average and Standard Deviation of PingErrorRatio from baseline data against given day and hour
SELECT @AveragePingErrorRatio = AVG(ErrorRatio),
@PingErrorRatioStandardDeviation = STDEVP(ErrorRatio)
FROM [#PingErrorDataset]
WHERE [Day] < @Day
AND [Hour] = @Hour

-- Calculation of PingErrorRatio StandardValue
--Formula = (PingErrorRatio of current hour - AveragePingErrorRatio from baseline dataset) / PingErrorRatioStandardDeviation of baseline dataset
IF EXISTS (
SELECT TOP 1 *
FROM #AlertThreshold
WHERE ThresholdCode = 'SD'
)
BEGIN
SELECT @SDThresholdValue = CAST(ThresholdValue AS INT),
@SDOperator = Operator
FROM #AlertThreshold
WHERE ThresholdCode = 'SD'

SET @PingErrorRatioSDValue = (@ErrorRatio - @AveragePingErrorRatio) / @PingErrorRatioStandardDeviation
SET @Query += CAST(@PingErrorRatioSDValue AS VARCHAR(250)) + ' ' + @SDOperator
+ ' ' + CAST(@SDThresholdValue AS VARCHAR(250))
END
-- Getting AffectedPings threshold value
IF EXISTS (
SELECT TOP 1 *
FROM #AlertThreshold
WHERE ThresholdCode = 'AP'
)
BEGIN
SELECT @APThresholdValue = CAST(ThresholdValue AS INT),
@APOperator = Operator
FROM #AlertThreshold
WHERE ThresholdCode = 'AP'

IF LEN(@Query) > 1
BEGIN
SET @Query += ' AND '
END

SET @Query += CAST(@AffectedPings AS VARCHAR(250)) + ' ' + @APOperator +
' ' + CAST(@APThresholdValue AS VARCHAR(250))
END
-- Getting AffectedPingPercentage threshold value
IF EXISTS (
SELECT TOP 1 *
FROM #AlertThreshold
WHERE ThresholdCode = 'APP'
)
BEGIN
SELECT @APPThresholdValue = CAST(ThresholdValue AS INT),
@APPOperator = Operator
FROM #AlertThreshold
WHERE ThresholdCode = 'APP'

IF LEN(@Query) > 1
BEGIN
SET @Query += ' AND '
END

SET @Query += CAST(@ErrorRatio AS VARCHAR(250)) + ' ' + @APPOperator +
' ' + CAST(@APPThresholdValue AS VARCHAR(250))
END

IF LEN(@Query) > 1
BEGIN
SET @Query = 'IF ' + @Query +
' BEGIN SELECT @IsAlert=1 END ELSE BEGIN SELECT @IsAlert=0 END'
END

DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N'@IsAlert BIT OUTPUT'
EXECUTE sp_executesql @Query,@ParmDefinition,@IsAlert = @IsAlert OUTPUT

IF @IsAlert = 1
BEGIN
INSERT INTO PingErrorAlertData
SELECT [BaselineId],
[Day],
[Hour],
[PingCount],
[ErrorCount],
[ErrorRatio],
@TraceId AS TraceId,
@IsAlert AS IsAlertHour
FROM #PingErrorDatasetHour
UNION ALL
SELECT [BaselineId],
[Day],
[Hour],
[PingCount],
[ErrorCount],
[ErrorRatio],
@TraceId AS TraceId,
0 AS IsAlertHour
FROM #PingErrorDataset
END
SELECT @PingErrorRatioSDValue AS StandardDeviation,
@AffectedPings AS AffectedPing,
@ErrorRatio AS AffectedPingPercentage,
@DatasetCount AS BaselineDataPointsCount
END


Thanks in advance

Answer

It works fine.

insert into  #TagsTable
    SELECT *
    FROM OPENROWSET('SQLNCLI','server=DB\CYBERREPORTING;Trusted_Connection=yes','SET NOCOUNT ON;SET FMTONLY OFF;declare @isAlert bit; EXEC MonitoringDB.dbo.ProcessPingErrorAlert_SingleRow 1,1,''2016-02-02'',1583,@IsAlert = @isAlert output')