CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Morph
Morph

Reputation: 39

Create a stored procedure with an IF/Else conditional that inserts into a table

I'm given a problem I've never seen before. That asks to create a procedure that Inserts a new book into the Books table. This procedure has to use an If/Else condition where the IF will handle the insert and the ELSE will Raise an error.

I'm having trouble with the If/Else because the way I've always done it in the past has been to use the If to throw an error while the else does the insert. It's unfinished but this is as far as I've gotten.

CREATE PROC spInsertBook
            @BookID        INT = NULL,
            @BookISBN      VARCHAR(50) = NULL,
            @BookTitle     VARCHAR(50) = NULL,
            @BookAuthor    VARCHAR(50) = NULL,
            @BookPublisher VARCHAR(50) = NULL,
            @BookGenre     VARCHAR(50) = NULL
AS
IF NOT EXISTS (SELECT * FROM Books WHERE BookID = @BookID)
    THROW 50001, 'BookID already exists!', 1;
ELSE
    SET BookID = @BookID
IF EXISTS (SELECT * FROM Books WHERE BookISBN = @BookISBN)
    THROW 50001, 'BookISBN already exists!', 1;
IF @BookTitle IS NULL
    THROW 50001, 'Enter a book title!', 1;
IF @BookAuthor IS NULL
    THROW 50001, 'Enter a book author!', 1;
IF @BookPublisher IS NULL
    THROW 50001, 'Enter a book publisher!', 1;
IF @BookGenre IS NULL
    THROW 50001, 'Enter a book genre!', 1;
ELSE
    INSERT Books
    VALUES(@BookID, @BookISBN, @BookTitle, @BookAuthor, @BookPublisher, @BookGenre)

You can see I use the If statements to throw the errors and the else to insert the input parameters, but this needs to be reversed, any ideas?

Upvotes: 0

Views: 1682

Answers (1)

Dale K
Dale K

Reputation: 27290

Are you looking for something like this? Where you do a full positive test first prior to error handling?

    CREATE PROC spInsertBook
    (
      @BookID        INT = NULL,
      @BookISBN      VARCHAR(50) = NULL,
      @BookTitle     VARCHAR(50) = NULL,
      @BookAuthor    VARCHAR(50) = NULL,
      @BookPublisher VARCHAR(50) = NULL,
      @BookGenre     VARCHAR(50) = NULL
    )
    AS
    begin
      set nocount on;

      if not exists (SELECT * FROM Books WHERE BookID = @BookID)
        and not exists (SELECT * FROM Books WHERE BookISBN = @BookISBN)
        and @BookTitle IS not NULL
        and @BookAuthor IS not NULL
        and @BookPublisher IS not NULL
        and @BookGenre IS not NULL
      begin
        INSERT Books (BookID, BookISBN, BookTitle, BookAuthor, BookPublisher, BookGenre)
        VALUES (@BookID, @BookISBN, @BookTitle, @BookAuthor, @BookPublisher, @BookGenre)
      end else  begin
        if exists (SELECT * FROM Books WHERE BookID = @BookID)
        begin
          THROW 50001, 'BookID already exists!', 1;
        end else if exists (SELECT * FROM Books WHERE BookISBN = @BookISBN)
        begin
          THROW 50001, 'BookISBN already exists!', 1;
        end else if @BookTitle IS NULL
        begin
          THROW 50001, 'Enter a book title!', 1;
        end else if @BookAuthor IS NULL
        begin
          THROW 50001, 'Enter a book author!', 1;
        end else if @BookPublisher IS NULL
        begin
          THROW 50001, 'Enter a book publisher!', 1;
        end else if @BookGenre IS NULL
        begin
          THROW 50001, 'Enter a book genre!', 1;
        end
      end

      return 0;
    end

Upvotes: 4

Related Questions