Code-Bin #774

untitled SQL Code
Posted by: PJ | August 4, 2010 @ 2:55am

SQL Code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SP_Update_Device]
	-- Add the parameters for the stored procedure here
	@P_IEEE varchar(50), @P_Current_val Decimal, @P_Type int
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

IF EXISTS (SELECT IEEE
	FROM Devices
	WHERE IEEE = @P_IEEE)
	
		IF (SELECT MaxTemp FROM Devices WHERE IEEE = @P_IEEE) < @P_Current_val

UPDATE Devices
				SET CurrentValue = @P_Current_val,
				Total = Total + @P_Current_val,
				Count_Total = Count_Total + 1,
				MaxTemp = @P_Current_val
				WHERE IEEE = @P_IEEE

ELSE IF (SELECT MinTemp FROM Devices WHERE IEEE = @P_IEEE) > @P_Current_val

UPDATE Devices
				SET CurrentValue = @P_Current_val,
				Total = Total + @P_Current_val,
				Count_Total = Count_Total + 1,
				MinTemp = @P_Current_val
				WHERE IEEE = @P_IEEE

ELSE
		
			INSERT INTO Devices (IEEE, FK_Type, CurrentValue, Total, Count_Total, MaxTemp, MinTemp)
			values (@P_IEEE, @P_Type, @P_Current_val, @P_Current_val ,1, @P_Current_val, @P_Current_val);
		
END

Syntax Highlighting

[Open in new window]

Author Comments

none

Rating

4.51 / 8
39 Votes