-3

I have three tables:

  • TB1 : P (p_id (pk),p_name,p_description)
  • TB2: PV (pv_id (pk),p_id(fk),v_id(fk),cost)
  • TB3: V (V_id (pk),name)

I have used below SQL Server stored procedure "Addp" which inserts p_name and p_description on being called.

CREATE PROCEDURE AddP 
    (@name CHAR(50), 
     @description VARCHAR(255)) 
AS
BEGIN
    DECLARE @PCount INT

    SELECT @PCount = Count(P_ID) 
    FROM P
    WHERE P_Name= @name

    IF @PCount = 0
    BEGIN
        INSERT INTO P(P_Name, P_Description)
        VALUES(@name, @description)
    END

    RETURN @@IDENTITY
END

Now, I would like to create another procedure where I can insert "v_id" and cost along with "Addp" stored procedure.My second SP looks like following. My question is How should I call my first stored procedure into second new stored procedure. So that when I execute my second stored procedure I should be able to insert p_name,p_description,vi,cost everything in one shot?

CREATE PROCEDURE AddPWithV (@vi int, @cost decimal(12,4))
AS
BEGIN

        INSERT INTO PV (V_ID, Cost)
        VALUES(@vi, @cost)
    Return @@IDENTITY
END;

I am new to stored procedure.So,please guide me through explanation. Any help is appreciated! Thanks in advance!

biggboss2019
  • 220
  • 3
  • 8
  • 30
  • what does more filed mean? – A Farmanbar Nov 24 '17 at 16:46
  • @Mr.AF.. It was typo. It has to to be fields..so that I can call the procedure and can insert values for those fields – biggboss2019 Nov 24 '17 at 16:48
  • You can't add data for v_id and cost columns if the columns don't exist in P to host them. – TJB Nov 24 '17 at 16:56
  • 2
    Don't use `@@IDENTITY`, instead use `SCOPE_IDENTITY()` - see https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide – Rory Nov 24 '17 at 16:59

1 Answers1

0

Just declare twor more variables Shown as below:

DECLARE @_vid INT
DECLARE @_cost numeric(18,2) // as per your column daya type 

Select @_vid =vid from TB3 where name =@name ;

Select  @_cost = cost from TB2 where 
P_id in (select p_id from TB1 where name =@name );

Now you have both values you can insert where ever you want ..

Renish vekariya
  • 81
  • 2