CODEWITHSUNDEEP
sqlsql-serverstored-procedures
shadow
shadow

Reputation: 55

Stored Procedure Update If Exists and greater than the existing value

I want to Update table TblNames if the name = @name exists update the columns comparing with existing row data.

Table (TblNames)

id | name  | val1 | val2 | val3 
1  | name1 |  10  |  12  |  13
2  | name2 |  11  |  13  |  15
3  | name3 |  15  |  12  |  11
4  | name4 |  10  |  12  |  13

parameters @name, @val1, @val2, @val3

If name exists select row and

compare val1 with @val1 parameter 

if val1 < @val1 
then @val1 = val1
else val1 = @val1

compare val2 with @val2 parameter 

if val2 > @val2 
then @val2 = val2
else val2 = @val2

Average of val3 and @val3 parameter 

( val3 + @val3 ) / 2

Finally

Update TblNames set val1 = @val1, val2 = @val2, val3 = @val3 Where name = @name

Upvotes: 0

Views: 1175

Answers (3)

Ajeet Verma
Ajeet Verma

Reputation: 1123

Please try below query to create procedure for your problem...

CREATE PROC dbo.Name_Calculation_value (
    DECLARE @name VARCHAR(20) = NULL
    ,@val1 INT = NULL
    ,@val2 INT = NULL
    ,@val3 INT = NULL
    )

BEGIN
    AS

    IF EXIST(SELECT COUNT(*) FROM dbo.TblNames) > 0
    BEGIN
        UPDATE dbo.TblNames
        SET val1 = CASE 
                WHEN val1 < @val1
                    THEN @val1
                ELSE val1
                END
            ,val2 = CASE 
                WHEN val2 <= @val2
                    THEN @val2
                ELSE val2
                END
            ,val3 = (val3 + @val3) / 2
        WHERE name = @name;
    END

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Use IIF:

UPDATE TblNames 
SET val1 = IIF(val1 < @val1, @val1, val1),
    val2 = IIF(val2 > @val2, @val2, val2), 
    val3 = (val3 + @val3) / 2
OUTPUT inserted.Val1, inserted.Val2, inserted.Val3
WHERE name = @name

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

You can use CASE expressions for val1 and val2 and just the arithmetic expression for val3.

UPDATE tblnames
       SET val1 = CASE
                    WHEN val1 >= @val1 THEN
                      @val1
                    ELSE
                      val1
                  END,
           val2 = CASE
                    WHEN val2 <= @val2 THEN
                      @val2
                    ELSE
                      val2
                  END,
           val3 = (val3 + @val3) / 2
       WHERE name = @name;

Upvotes: 1

Related Questions