Stored Procedure not working correctly

Question

I am working on an assignment and I have to write a stored procedure in Microsoft SQL Server. The exact code is given in the book and I have checked it several times to make sure I didn't miss anything but I'm not getting the right results when I query it.

Here is the code for the procedure:

CREATE PROCEDURE InsertCustomerWithTransaction
      @NewCustomerLastName            char(25),
      @NewCustomerFirstName           char(25),
      @NewCustomerAreaCode            char(3),
      @NewCustomerPhoneNumber         char(8),
      @NewCustomerEmail               varchar(100),
      @ArtistLastName                 char(25),
      @WorkTitle                      char(25),
      @WorkCopy                       char(12),
      @transSalesPrice                numeric(8,2)
AS
   DECLARE
      @RowCount       AS Int,
      @ArtistID       AS Int,
      @CustomerID     AS Int,
      @WorkID         AS Int,
      @TransactionID  AS Int

   SELECT @RowCount = count(*)
   FROM Customer
   WHERE LastName = @NewCustomerLastName
     AND FirstName = @NewCustomerFirstName
     AND AreaCode = @NewCustomerAreaCode
     AND PhoneNumber = @NewCustomerPhoneNumber
     AND Email = @NewCustomerEmail

   IF (@RowCount > 0)
   BEGIN
            PRINT '************************************************'
            PRINT ''
            PRINT '  The Customer is already in the database. '
            PRINT ''
            PRINT '  Customer Last Name  = ' + @NewCustomerLastName
            PRINT '  Customer First Name  = ' + @NewCustomerFirstName
            PRINT ''
            PRINT '************************************************'
        RETURN
      END   

      ELSE
         BEGIN TRANSACTION
                INSERT INTO Customer
        (LastName, FirstName, AreaCode, PhoneNumber, Email)
        VALUES( @NewCustomerLastName, @NewCustomerFirstName, @NewCustomerAreaCode, @NewCustomerPhoneNumber, @NewCustomerEmail)

        SELECT @CustomerID = CustomerID
        FROM Customer
        WHERE LastName = @NewCustomerLastName
         AND FirstName = @NewCustomerFirstName
         AND AreaCode = @NewCustomerAreaCode
         AND PhoneNumber = @NewCustomerPhoneNumber
         AND Email = @NewCustomerEmail

         SELECT @ArtistID = ArtistID
         FROM Artist
         WHERE LastName = @ArtistLastName

IF @ArtistID is null
   Begin
     PRINT '************************************************'
     PRINT ''
     PRINT '    Invalid ArtistID'
     PRINT ''
     PRINT '************************************************'
     ROLLBACK TRANSACTION
     RETURN
END

SELECT @WorkID = WorkID
FROM Work
WHERE ArtistID = @ArtistID
AND   Title = @WorkTitle
AND   Copy = @WorkCopy

  IF @WorkID is null
     BEGIN
           PRINT '************************************************'
           PRINT ''
           PRINT '    Invalid WorkID'
           PRINT ''
           PRINT '************************************************'
        ROLLBACK TRANSACTION
       RETURN
   END

   SELECT @TransactionID = TransactionID
   FROM Trans
   WHERE WorkID = @WorkID
   AND   SalesPrice = null

    IF @TransactionID is null
      BEGIN
           PRINT '************************************************'
           PRINT ''
           PRINT '    Invalid TransactionID'
           PRINT ''
           PRINT '************************************************'
        ROLLBACK TRANSACTION
       RETURN
     END

     BEGIN 
       UPDATE Trans
       SET   DateSold = getDate(),
             SalesPrice = @TransSalesPrice,
             CustomerID = @CustomerID
       WHERE TransactionID = @TransactionID

       INSERT INTO CUSTOMER_ARTIST_INT(CustomerID, ArtistID)
            VALUES (@CustomerID, @ArtistID)
      END

      COMMIT TRANSACTION

        BEGIN


           PRINT '************************************************'
           PRINT ''
           PRINT '  The Customer is now in the database. '
           PRINT ''
           PRINT '  Customer Last Name  = ' + @NewCustomerLastName
           PRINT '  Customer First Name  = ' + @NewCustomerFirstName
           PRINT ''
           PRINT '************************************************'     

           PRINT '************************************************'
           PRINT ''
           PRINT '   Transaction Complete. '
           PRINT ''
           PRINT '    TransactionID =  ' +convert(char(6), @TransactionID)
           PRINT '    ArtistID      =  ' +convert(char(6), @ArtistID)
           PRINT '    WorkID        =  ' +convert(char(6), @WorkID)
           PRINT '    Sales Price   =  ' +convert(char(12), @TransSalesPrice)
           PRINT ''
           PRINT '************************************************'

           PRINT '************************************************'
           PRINT ''
           PRINT '   New Customer_Artist_Int row added. '
           PRINT ''
           PRINT '    ArtistID      =  ' +convert(char(6), @ArtistID)
           PRINT '    CustomerID    =  ' +convert(char(6), @CustomerID)
           PRINT ''
           PRINT '************************************************'
END 

When I try to test it using the following code which was also given in the book I get the "Invalid TransactionID" message from the procedure. According to the book the customer should be added successfully.

EXEC InsertCustomerWithTransaction
   @NewCustomerLastName = 'Gliddens',
   @NewCustomerFirstName = 'Melinda',
   @NewCustomerAreaCode = '360',
   @NewCustomerPhoneNumber = '765-8877',
   @NewCustomerEmail = 'Melinda.Gliddens@somewhere.com',
   @ArtistLastName = 'Sargent', @WorkTitle = 'Spanish Dancer',
   @WorkCopy = '588/750', @TransSalesPrice = 350.00;

Any ideas what I have missed? Is there any workaround I could use?

Answer 1

You can use SalesPrice = NULL but you need to set ANSI_NULL OFF by Default it is ON

SET ANSI_NULLS OFF

SalesPrice = NULL

SET ANSI_NULLS ON

when You will set it OFF

it means ISO Standard is being followed means

= and <> should can be used for null comparison.

Answer 2

You should use SalesPrice IS NULL, not SalesPrice = NULL

SalesPrice=NULL won't throw an error, but it also won't match like you think.