-1

I am using SQL Server 2014 in which i had designed the database to work on the General store management.

I have to input the item along with its Supplier name its stock and Name of item. I have to execute three statements for this purpose, that is why I am using the transaction option as I need to roll back again if there is any type of problem during the implementation. Here is the SQL which I have been facing many issues with.

Here is the SQL code that I am trying to run:

BEGIN TRANSACTION AddItem

INSERT INTO Product(Name, Stock, Type_Id, Pur_Price, Sale_Price)
VALUES ('Lemon', 20, 2, 129, 325);

INSERT INTO Supplier(Name, Contact_No)
VALUES ('Kamran', '034637827');

INSERT INTO Purchase(Product_id, Supplier_Id, Quantity)
VALUES(EXEC spGetProductId @Name= 'Lemon', EXEC spGetSupplierId @Name='Kamran', 20);

COMMIT AddItem
ROLLBACK
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Software Engineer
  • 145
  • 10
  • 1
    You are facing many issues? Care to share them? Are there syntax errors? No errors, but the data is incorrect? – dfundako Dec 04 '18 at 17:44
  • 1
    You can't just stick a stored procedure into the list of values in an insert statement like that. And you seem to have some pretty serious architecture issues here. Using the name of the product and/or supplier to get the ID is a sign that something is quite wrong. That means you have can't have any duplicates in those columns which is pretty strange. – Sean Lange Dec 04 '18 at 17:52

1 Answers1

0

Couple of issues:

  1. Cannot use stored procedure output in a values clause.
  2. Why do you have commit and rollback
  3. Are you executing this from SSMS, some C# app etc?
  4. what does sp_getproductid return resultset or return value?

Maybe something like this would work for SSMS or if you plan to make a stored procedure out of it

    BEGIN TRY
        BEGIN TRANSACTION 

            INSERT INTO Product(Name, Stock, Type_Id, Pur_Price, Sale_Price)
            VALUES ('Lemon', 20, 2, 129, 325);

            INSERT INTO Supplier(Name, Contact_No)
            VALUES ('Kamran', '034637827');

            DECLARE @prodid int
            EXEC @prodid = spGetProductId @Name= 'Lemon'

            DECLARE @SupplierID int
            EXEC @SupplierID = spGetSupplierId @Name='Kamran'

            INSERT INTO Purchase(Product_id, Supplier_Id, Quantity)
            VALUES(@prodid , @SupplierID, 20);

        COMMIT
    END TRY
    BEGIN CATCH
        DECLARE @ErrCode        INT = 0,
                @ErrMsg         VARCHAR(4000) = '',
                @CRLF           CHAR(2) = CHAR(13) + CHAR(10)

        SET @ErrCode = @@ERROR;
        SET @ErrMsg = ERROR_MESSAGE();

        IF XACT_STATE() = -1
            ROLLBACK TRANSACTION;
        ELSE IF @@TRANCOUNT = 1 
            ROLLBACK TRANSACTION;
        ELSE IF @@TRANCOUNT > 1 AND XACT_STATE() = 1 
            COMMIT;

        RAISERROR(@ErrMsg, 16, 1);
    END CATCH
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel N
  • 1,122
  • 1
  • 8
  • 14
  • After entering this query Following error is recieved: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Purchase_Product". The conflict occurred in database "GeneralStoreManagement", table "dbo.Product", column 'Product_id'. – Software Engineer Dec 05 '18 at 05:02
  • I am using the SQL Server database for making a general store management, spGetProductId is returning an integer that is id of that product to enter in the purchase table. Now keep in mind that i am inputting the product in product table and putting the id of that product that is being entered in this transaction in Purchase table. That is why it is giving me the foreign key Conflict that product doesn't exists. Please reply soon as soon as possible. – Software Engineer Dec 05 '18 at 06:48