通过执行更新代码

Question

我是一名初级程序员，我正在尝试学习SQL，我在这里遇到了一个问题，我被给予的问题是:

编写一个过程UpdateSuggestedPrice，接受一个ISBN号和一个新的建议价格。如果ISBN不存在，则引发错误消息。如果没有错误，将建议价格更新为新价格。

Create PROCEDURE UpdateSuggestedPrice (@ISBN char(10), @SuggestedPrice smallmoney)
AS
BEGIN
   if @ISBN is null
   Begin
     Raiserror ('Provide a valid ISBN',16,1)
   End
   else
   Begin    
     if (select COUNT(*) from Title where SuggestedPrice = @SuggestedPrice) = 0
     begin
        Select 'Description sucessfully added'
        insert into Title (SuggestedPrice)
        values (@SuggestedPrice)
     End
     Else
        Raiserror ('Description already exists',16,1)
     End
   End
   Return
-- Here I'm trying to execute the procedure, search for ISBN and 
-- then update the suggested price, can someone please tell me 
-- what I'm doing wrong.
execute UpdateSuggestedPrice @ISBN= '1021031040', @SuggestedPrice = '40'

Answer 1

您的代码有几个问题。首先，您正在搜索SuggestedPrice = @SuggestedPrice中记录的数据-这实际上应该像ISBN = @ISBN中一样查找ISBN。在存储过程的末尾还缺少一个END。

我建议你再读一遍这个问题。

CREATE PROCEDURE UpdateSuggestedPrice (@ISBN char(10), @SuggestedPrice smallmoney)
AS
BEGIN
   IF @ISBN is null
   BEGIN
     RAISERROR ('Provide a valid ISBN',16,1)
   END
   ELSE
   BEGIN    
     IF (SELECT COUNT(*) FROM Title WHERE ISBN = @ISBN) = 0
     BEGIN
        RAISERROR ('ISBN does not exist.',16,1)
     END
     ELSE
     BEGIN
        SELECT 'Price sucessfully updated.';
        UPDATE Title /* Title is the table to be updated */
        SET SuggestedPrice = @SuggestedPrice /* this is the field to update */
        WHERE ISBN = @ISBN; /* this selects which record to update */
     END
   END
END 
GO
EXECUTE UpdateSuggestedPrice @ISBN= '1021031040', @SuggestedPrice = '40'