SQL Server:创建和调用存储过程 [英] SQL Server : create and call stored procedure

问题描述

我该如何正确书写?

我正在编写一个存储过程，它将采用 (StockName, NewOpenPrice, NewClosePrice)，并向表中添加一条新记录(如图所示)，如果股票名称不存在.如果股票名称存在，则 OpenPrice 和 ClosePrice 将使用新插入的价格进行更新.最后我要调用存储过程

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

这是现在的样子

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)

推荐答案

我认为这就是你所需要的

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 

或者使用Merge代替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) 

执行

  exec P_updatestock 'PFE',22.34,32.45

这篇关于SQL Server:创建和调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！