Haminteu Haminteu - 2 months ago 5
SQL Question

Execute stored procedure SQL using value from table

Check my table below:

ID Number01 Number02 Number03
-----------------------------------
1 10 20 4510
2 5 2 545
3 4 4 664
4 10 1 NULL
5 1 4 NULL


"Number03" field is a calculated field which is Number01 + Number02. I am using a stored procedure to calculate it. Why I am using a stored procedure? Because I have the interface which made by asp.net.

This is the stored procedure:

ALTER PROCEDURE _mySP
(@Number01 decimal(18,0), @Number02 decimal(18,0))
AS
BEGIN
DECLARE @myCOM1 float
DECLARE @myCOM2 float

SET @myCOM1 = @Number1 + 500
SET @myCOM2 = POWER(@Number2, 2) * 10

INSERT INTO _myTable(Number01, Number02, Number03)
VALUES (@Number01, @Number02, @myCOM1 + @myCOM2)
END


The question is, how can I execute the stored procedure without entering the value one by one? Because the value is already in the table. I want to update all the null value on "Number03" field. Also any idea how execute my question using CURSOR?

EDIT: It seems that my previous question is too simple. So I make it complex a little bit.

Answer

Since you're using SQL Server 2008, you can use a computed field to generate the value from the other two fields.

If you just want to update the table without doing this, you could use the following SQL:

UPDATE _myTable SET Number03 = ISNULL(Number01,0) + ISNULL(Number02,0)

This will update all rows.

You could update the SP to take a 3rd param:

ALTER PROCEDURE _mySP
    (
      @Number01 DECIMAL(18, 0) ,
      @Number02 DECIMAL(18, 0) ,
      @IsUpdate BIT = 0
    )
AS
    BEGIN
        IF ( @IsUpdate = 0 )
            BEGIN
                INSERT  INTO _myTable
                        ( Number01 ,
                          Number02 ,
                          Number03
                        )
                VALUES  ( @Number01 ,
                          @Number02 ,
                          ISNULL(@Number01,0) + ISNULL(@Number02,0)
                        )
            END
        ELSE
            BEGIN
                UPDATE  _myTable
                SET     Number03 = ISNULL(Number01,0) + ISNULL(Number02,0)
                WHERE Number03 IS NULL
            END
    END

EDIT: I have added ISNULL to the calculation for any numbers that are null, it will use 0 instead.

Comments