Is this T-SQL stored procedure written correctly?

Question

I am new to SQL Server and T-SQL, but I do have some experience building applications in MS Access.

This stored procedure runs fine when I execute it from the application, however when I am debugging it in SSMS, I get an error

Unable to Step. Invalid Operation.

but it will allow me to step through. Based on my research, it seems like I am creating a race condition but I have not been able to correctly fix the issue. I would also appreciate any advice to optimize this or fix any issues that are apparent.

What the code does:

This code is to enter a new customer into the database. The first select statement looks for an existing ID. If the ID is null, it will add a new customer. If the ID is not null, it will not add the customer. The same goes for the second IF statement to check if @pName2 and @pName3 are null before inserting these values.

Here is my code:

    @pUID nvarchar(16) = null, 
    @pName1 nvarchar(50) = null,
    @pName2 nvarchar(50) = null,
    @pName3 nvarchar(50) = null,
    @pAddress1 nvarchar(30) = null,
    @pAddress2 nvarchar(30) = null,
    @pAddress3 nvarchar(30) = null,
    @pZipCode nvarchar(30) = null
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ID INT
    SELECT @ID = ID FROM tblCustomer WHERE strUID = @pUID

    IF @ID IS NULL
    BEGIN 
        DECLARE @Customer_ID INT

        INSERT INTO tblCustomer(strUID, strName)
        VALUES(@pUID, @pName1)

        SET @Customer_ID = @@IDENTITY

        IF (@pName2 <> '') OR (@pName3 <> '')
        BEGIN
            INSERT INTO tblSecondaryCustomer(CustomerID, strName2, strName3)
            VALUES(@Customer_ID, @pName2, @pName3)
        END

        INSERT INTO tblAddress(CustomerID, strAddress1, strAddress2, strAddress3, strZipCode)
        VALUES(@Customer_ID, @pAddress1, @pAddress2, @pAddress3, @pZipCode)
    END
END

Answer 1

Try replacing your IF statement with the following:

IF NOT EXISTS(SELECT ID FROM tblCustomer WHERE strUID = @pUID)

It doesn't seem your using @ID other than a check for existence...and you can use the ISNULL function to make sure you cover NULL cases...

IF (ISNULL(@pName2,'') <> '') OR (ISNULL(@pName3,'') <> '')

HTH

Dave