Stored Procedure error SQL Server 2014

Question

I'm having some trouble with a stored procedure I'm running in SQL. It's for a class I'm taking and the stored procedure is from the textbook, but the textbook has had a lot of errors in their SQL code so I wouldn't be surprised if that was the case here, too. I asked the professor and he seems to be having trouble figuring it out as well.

Here is the stored procedure I'm using, as generated by SQL Server Management Studio's "Create to" function:

USE [VGA]
GO

/****** Object:  StoredProcedure [dbo].[InsertCustomerWithTransaction]    Script Date: 4/8/2015 7:55:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertCustomerWithTransaction]
    @NewCustomerLastName    CHAR(25),
    @NewCustomerFirstName   CHAR(25),
    @NewCustomerAreaCode    CHAR(3),
    @NewCustomerPhoneNumber CHAR(8),
    @NewCustomerEmail       VARCHAR(100),
    @ArtistLastName         CHAR(25),
    @WorkTitle              CHAR(35),
    @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    dbo.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 '     CustomerLastName = '+@NewCustomerLastName
            PRINT '     CustomerFirstName = '+@NewCustomerFirstName
            PRINT ''
            PRINT '****************************************************'
        END

        ELSE
            BEGIN TRANSACTION
                INSERT INTO dbo.CUSTOMER
                    (LastName, FirstName, AreaCode, PhoneNumber, Email)
                    VALUES
                    (@NewCustomerLastName, @NewCustomerFirstName, @NewCustomerAreaCode, @NewCustomerPhoneNumber, @NewCustomerEmail)
                SELECT @CustomerID = CustomerID
                FROM dbo.CUSTOMER
                WHERE   LastName = @NewCustomerLastName
                    AND FirstName = @NewCustomerFirstName
                    AND AreaCode = @NewCustomerAreaCode
                    AND PhoneNumber = @NewCustomerPhoneNumber
                    AND Email = @NewCustomerEmail
        SELECT  @ArtistID = ArtistID
        FROM    dbo.ARTIST
        WHERE   LastName = @ArtistLastName

        IF @ArtistID IS NULL
            BEGIN
                PRINT '****************************************************'
                PRINT ''
                PRINT '     Invalid ArtistID    '
                PRINT ''
                PRINT '****************************************************'
                ROLLBACK TRANSACTION
                RETURN
            END

        SELECT  @WorkID = WorkID
        FROM    dbo.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
        ELSE
            BEGIN
                PRINT '****************************************************'
                PRINT ''
                PRINT '     WorkID = '+CONVERT(CHAR(6), @WorkID)
                PRINT ''
                PRINT '****************************************************'
            END

        SELECT  @TransactionID = TransactionID
        FROM    dbo.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  dbo.TRANS
            SET     DateSold = GETDATE(),
                    SalesPrice = @TransSalesPrice,
                    CustomerID = @CustomerID
            WHERE   TransactionID = @TransactionID
            INSERT INTO dbo.CUSTOMER_ARTIST_INT (CustomerID, ArtistID)
                VALUES (@CustomerID, @ArtistID)
        END

    COMMIT TRANSACTION
        BEGIN
            PRINT '****************************************************'
            PRINT ''
            PRINT '     The new 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
GO

And here is the code that I'm running:

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;

When I run the code, it works fine up until the section that starts with

SELECT  @TransactionID = TransactionID

At this point it gives me the error "Invalid TransactionID". Since the input requires the WorkID and that the SalesPrice column be NULL, I added a bit of code to give me the WorkID at this point, and the output from this was the correct WorkID. screenshot I went into the database and double-checked that the SalesPrice column in that row is NULL, and it is. screenshot But for some reason the stored procedure cannot find the correct TransactionID, even though I can manually find it in the table. Can someone help me figure out what I'm doing wrong here? Thank you!

Answer 1

CRLast86....just wanted to point out, that = Null and Is Null are both valid. IS NULL is the ANSI standard, but MS-SQL allows = Null, except when you have changed the ANSI_NULLS mode, as you have, with the SET ANSI_NULLS ON statement near the top. Best practice says, use SET ANSI_NULLS ON so that it will not let you use = Null. So, in the future when you see an = Null and it is working, you will know that conditionally, both are valid. Do a search on SET ANSI_NULLS ON and you will get the whole long story.

Answer 2

In SQL SERVER, you should use "IS NULL" not "= NULL" to check null value.

SELECT  @TransactionID = TransactionID
FROM    dbo.TRANS
WHERE   WorkID = @WorkID
    --AND SalesPrice = NULL
      AND SalesPrice is NULL

Try it again. Hope it helps.

Answer 3

It's IS NULL not = NULL

in this bit

SELECT  @TransactionID = TransactionID
        FROM    dbo.TRANS
        WHERE   WorkID = @WorkID
            AND SalesPrice = NULL