CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Neha Bhatt
Neha Bhatt

Reputation: 67

Insert inside a stored procedure

I am writing an insert statement inside a stored procedure. When I am inserting duplicate value for SKU, I am not getting

Error occurred while trying to insert the ProductPrice table

and @errValue.

I don't know why? It's just saying statement terminated 

ALTER PROCEDURE HW5INS1
    -- PRODUCTPRICE TABLE: LEVEL-1
    @SKU VARCHAR(64),
    @startDate DATE,
    @endDate DATE = NULL,---NOT REQUIRED
    @price SMALLMONEY
AS
BEGIN TRANSACTION
    -- Test For Existence Of Parent Row: LOOK INTO PRODUCTS TABLE
    IF EXISTS (SELECT SKU FROM Products WHERE SKU = @SKU)
    BEGIN
        INSERT INTO ProductPrices (SKU, startDate, endDate, price)
        VALUES (@SKU, @startDate, @endDate, @price)

        SELECT * FROM ProductPrices

        DECLARE @errValue INT
        SET @errValue = @@ERROR

        IF (@@ERROR <>0)
        BEGIN
            PRINT 'Error occurred while trying to insert the ProductPrice table'
            PRINT @errValue
            RETURN -11001
        END
    END
    ELSE
    BEGIN
        -- PARENT DOES NOT EXIST, RETURN ERROR
        PRINT 'Error: PARENT DOESNOT EXIST'
        RETURN -11002
    END

    COMMIT TRANSACTION
    RETURN

Upvotes: 0

Views: 123

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This is too long for a comment.

You are trying to replicate database functionality in a stored procedure. This is a bad idea. One is that -- due to race conditions -- you cannot do as well as the database. Second is that you might get this code right, but other code might update/insert/delete rows and cause problems.

The two things you want are a foreign key constraint and a unique constraint:

alter table ProductPrices add constraint fk_productprices_sku
    foreign key (sku) reference products(sku);

alter table ProductPrices add constraint unq_productprices_sku
    unique (sku);

You can capture the errors to rename them. That seems like superfluous effort (in my opinion). Just give the constrains meaningful names and let the database do its work.

Upvotes: 1

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

Try to rewrite your SP this way.

ALTER PROCEDURE HW5INS1

------PRODUCTPRICE TABLE: LEVEL-1
    @SKU varchar(64),
    @startDate date,
    @endDate date = NULL,---NOT REQUIRED
    @price smallmoney
AS


---------Test For Existence Of Parent Row: LOOK INTO PRODUCTS TABLE
If Exists(Select SKU from Products where SKU = @SKU)
      BEGIN
BEGIN TRANSACTION
begin try
         INSERT INTO ProductPrices (SKU, startDate, endDate, price)
VALUES (@SKU, @startDate, @endDate, @price)
commit
end try
begin catch
           --select * from ProductPrices
         DECLARE @errValue int
         SET @errValue=@@ERROR
rollback
         IF (@ERROR <>0)
           BEGIN
             PRINT 'Error Occured While Trying To Insert The ProductPrice Table'
             PRINT @errValue
             RETURN -11001
           END
end catch
      END
ELSE
  BEGIN
  -----PARENT DOESNOT EXIST, RETURN ERROR
    PRINT 'Error: PARENT DOESNOT EXIST'
    RETURN -11002
  END

RETURN

Upvotes: 0

Related Questions