Execute stored procedure SQL using value from table

Question

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.

Mabey with an update Statement: Update _mySP Set Number03 = Number01 + number02 — Tienou
– Tienou, Commented Jul 14, 2014 at 8:07

Christian Phillips · Accepted Answer · 2014-07-15 08:27:59Z

2

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.

edited Jul 15, 2014 at 8:27

answered Jul 14, 2014 at 8:19

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Phillips Over a year ago

The Update can change to ...WHERE Number03 IS NULL, you don't need a cursor though?

Michael Green · Accepted Answer · 2014-07-14 11:42:40Z

1

If you really want to use the SP to do the work you have to do it one at a time. Write a cursor to select all the rows with a NULL value and call the SP for each row passing Number01 and Number02.

I suspect your actual example is rather more complicated than the code you've shown. If you are able to go into more detail we may be able to suggest better approaches. If it is indeed this simple a computed column or simple update statement would be the way to go.

answered Jul 14, 2014 at 11:42

Michael Green

1,4911 gold badge19 silver badges27 bronze badges

3 Comments

Mark Over a year ago

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post.

Michael Green Over a year ago

@Mark - the OP explicitly states "The question is, how can i execute the storeprocedure without entering the value one by one" and I reply "you cannot." How is this not an answer to the question?

Michael Green Over a year ago

And here's me thinking it was about helping people understand and become better programmers, @Mark. My bad.

meer · Accepted Answer · 2014-07-15 05:24:54Z

1

You can use computed columns like this. It will automatically fill all data in number03 column.

CREATE TABLE _myTable(
ID INT IDENTITY(1,1),
Number01  INT,
Number02  INT,
Number03 AS (Number01 + Number02)
)

------OR if you already have table then

Alter table _myTable Drop column Number03;
Alter table _myTable Add Number03 AS (Number01 + Number02);

-------------EDITED ACCORDING TO YOUR EDITING IN QUESTIONS

You can still put this formula in computed column. But as you are asking another way, so You can edit your SP like this

ALTER PROCEDURE _mySP
AS
BEGIN
Update _myTable
set Number03 = ((Number01 + 500) + (POWER(Number02, 2) * 10))
WHERE Number03 is NULL;
END

--------Executing Stored procedure will update table without providing any parameters
Exec _mySP

edited Jul 15, 2014 at 5:24

answered Jul 14, 2014 at 11:43

meer

1323 bronze badges

Comments

I A Khan · Accepted Answer · 2014-07-14 08:30:20Z

0

Here is how you could do it:

CREATE TABLE #Test(
ID int,
Number1 decimal(18,0),
number2 decimal(18,0),
number3 decimal(18,0))

INSERT INTO #Test
VALUES
(1  , 10, 20, 30),
(2  , 5 , 2,  7),
(3  , 4,  4 , 8),
(4  , 10, 1,  NULL),
(5  , 1,  4,  NULL)

UPDATE #Test
Set Number3 = Number1 + number2

SELECT * FROM #Test
DROP TABLE #Test

Example at SQLFiddle

edited Jul 14, 2014 at 8:30

I A Khan

8,92716 gold badges56 silver badges78 bronze badges

answered Jul 14, 2014 at 8:22

Tienou

7206 silver badges17 bronze badges

Collectives™ on Stack Overflow

Execute stored procedure SQL using value from table

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related