SQL Server : create and call stored procedure

debugcn Published at Dev

Exceli

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ரதீப்

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