SQL Server : create and call stored procedure

Question

How do I write this correctly?

I am writing a stored procedure that will take (StockName, NewOpenPrice, NewClosePrice), and add a new record to the table (shown in image), IF the stockname does not EXIST. IF the stockname EXISTs, then OpenPrice and ClosePrice will be updated with the newly inserted prices. Finally I want to call the stored procedure

This is what it looks like now

CREATE PROCEDURE p_updatestock
    (
    @StockName VARCHAR(50), 
    @OpenPrice MONEY,
    @ClosePrice MONEY)
AS
    Declare @NewOpenPrice MONEY
    Declare @NewClosePrice MONEY

    UPDATE Stocks 
    SET StockName = @StockName
    SET @StockName = @rowcount

    UPDATE Stocks 
    SET NewOpenPrice = @NewOpenPrice
    WHERE OpenPrice = @NewOpenPrice 
    SET @NewOpenPrice = @rowcount

    IF (@StockName EXIST THEN OpenPrice)
        UPDATE Stocks 
        SET NewClosePrice = @NewClosePrice
        WHERE ClosePrice = @NewClosePrice
        SET @NewClosePrice = @rowcount

        IF (@StockName EXIST THEN ClosePrice)

enter image description here

Pரதீப் · Accepted Answer · 2015-01-10 08:26:00Z

2

I think this is what you need

CREATE PROCEDURE P_updatestock (@StockName  VARCHAR(50),
                                @OpenPrice  MONEY,
                                @ClosePrice MONEY)
AS
  BEGIN
      IF EXISTS (SELECT 1
                 FROM   Stocks
                 WHERE  StockName = @StockName)
        UPDATE Stocks
        SET    ClosePrice = @ClosePrice,
               Openprice = @Openprice
        WHERE  StockName = @StockName
      ELSE
        INSERT INTO stocks
                    (StockName,
                     Openprice,
                     ClosePrice)
        VALUES      (@StockName,
                     @Openprice,
                     @ClosePrice)
  END

Or use Merge instead of If-Else

MERGE Stocks AS target
USING (SELECT @StockName,
              @OpenPrice,
              @ClosePrice) AS source (StockName, OpenPrice, ClosePrice)
ON ( target.StockName = source.StockName )
WHEN MATCHED THEN
  UPDATE SET ClosePrice = source.ClosePrice,
             Openprice = source.Openprice
WHEN NOT MATCHED THEN
  INSERT (StockName,
          Openprice,
          ClosePrice)
  VALUES (source.StockName,
          source.Openprice,
          source.closeprice)

To execute

  exec P_updatestock 'PFE',22.34,32.45

edited Jan 10, 2015 at 8:26

answered Jan 10, 2015 at 8:17

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

3 Comments

Exceli Over a year ago

WOW! THANKS NoDisplayName. Let me run it and see. How do I call it?

Exceli Over a year ago

Got this error messageMsg 137, Level 15, State 2, Procedure P_updatespock, Line 10 Must declare the scalar variable "@NewClosePrice". Msg 137, Level 15, State 2, Procedure P_updatespock, Line 19 Must declare the scalar variable "@NewOpenprice".

Exceli Over a year ago

Alright. It worked. Thank you once again. I was thinking "Declare Openstock Money" and "Closestok Money" is what it needed.

Collectives™ on Stack Overflow

SQL Server : create and call stored procedure

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related