Click here to Skip to main content
15,923,083 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
I am trying to create a single SQL Server stored procedure in which I can input my own `c_id` and `COST` along with my previously created SQL Server stored procedure.

My tables are as follows:

* TB1: A (a_id (pk), a_name, a_description, a_qh)
* TB2: AC (ac_id (pk), a_id(fk), c_id(fk), ac_cost)
* TB3: C (c_id (pk), c_name)

My previously created procedure looks like following:

** NEW UPDATED CODE:**

CREATE PROCEDURE test (@name char(30), @description varchar(255), @VEid INT, @Cost as int) as
    BEGIN
    	DECLARE @A_id INT
  		INSERT INTO a(a_Name,a_description) VALUES (@name,@Description )
		Select @A_id = a_ID from A where a_Name = @name               
    	 Insert into AC(cost, A_ID, C_ID)  values(@Cost, @A_id, @VEid) 
     END

I would like to add that my A Table has below info.

a_id   a_name  a_description
1       Pen    THIS IS PEN
2       pAPER   IT IS PAPAR
3       GLASS   IT IS gLASS
14      RUBBER   THIS IS RUBBR


MY C table consist of
c_id    c_name
1        name1
2        nam2
3       name3

My AC table has followinf info
ac_id    cost     a_id  c_id
1	50.0000	   1	NULL
2	45.0000	   2	NULL
3	30.0000	   3	NULL
4	55.0000	   1	2
5	20.0000	  14	2
6	60.0000	   1	3
7	25.0000	  14	3


UPDATED AC table after running below query
1	15.0000	1	NULL
2	15.0000	2	NULL
3	15.0000	3	NULL
4	55.0000	1	2
5	20.0000	14	2
6	60.0000	1	3
7	25.0000	14	3


This is NOT what I was looking for. I was trying to insert new row with following info in ac tble
ac_id   cost      a_id    c_id
8       15      from a table   1

Thanks in advance!

What I have tried:

My dilemma is I am new to SQL Server stored procedures, and I am trying to insert my new 'c_id', and 'ac.cost' along with a_name and a_description. So, if you can guide me through explanation . That will be great.
After Executing below code my
Posted
Updated 27-Nov-17 15:03pm
v6

Try

SQL
CREATE PROCEDURE AProduct (@name char(30), @description varchar(255), @C_id numeric(18,0), @Cost as int)) as
    BEGIN
    	DECLARE @A_id INT
  		UPDATE A SET a_description=@Description WHERE a_name = @name  -- Update the description field only. Not sure if required
		Select @A_id = a_id from A where a_name = @name                 -- Retrieve id of the row with the name equal to the parameter inserted. Could use scope identity but for simplicity I have not.
    	 Insert into AC (cost, a_id, c_id)  values(@Cost, @A_id, @C_id) -- Insert into AC table parameter cost and C_id and the A_id from line above.
   -- 	 Insert into AC (cost, a_id, c_id) output inserted.ac_id values(@Cost, @A_ID, @C_id) -- If required
     END


I have not tested this, but hopefully it is what you are try to do.
 
Share this answer
 
v2
Comments
Member 13542464 26-Nov-17 21:29pm    
Ross Thanks for the effort. Good news is values are inserting in Table A that is A_NAME and A_description. However. it is not inserting in AC table where I need to insert my Cost and a_id. Please see above code titled "New Updated Code"
RossMW 26-Nov-17 21:39pm    
Try just running in sql to see what the errors are.. eg

Declare @name char(30) = 'something'
Declare @description varchar(255) = 'something'
Declare @C_id numeric(18,0) = 1
Declare @Cost as int = 55
DECLARE @A_id INT
UPDATE A SET a_description=@Description WHERE a_name = @name
Select @A_id = a_id from A where a_name = @name
Insert into AC (cost, a_id, c_id) values(@Cost, @A_id, @C_id)

These are basic sql commands so once they work in straight sql they can then be put into the stored procedure as required.
Member 13542464 26-Nov-17 21:54pm    
Ross see below Code: Everything is fine. But is is inserting values two times instead of 1. What could be reason ? I would like to clear that a table is my Product1 table, c IS my VENDOR table and AC is my Bridge table

DDeclare @name1 varchar(30) = 'bLACK HAT'
Declare @description1 varchar(255) = 'sKKK'
Declare @C_id INT = 1
Declare @Cost1 int = 55
DECLARE @A_id INT
UPDATE PRODUCT1 SET ProductDescription=@description1 WHERE ProductName = @name1
Select @A_id = Productid from PRODUCT1 where ProductName = @name1
Insert into VendorProduct(cost, ProductID, VendorID) values(@Cost1, @A_id, @C_id)
If you want to add additional parameters to the stored procedure then you just add them as follows

SQL
CREATE PROCEDURE AProduct (@name char(30), @description varchar(255), @C_id numeric(18,0), @Cost as int)) as
    BEGIN
    	DECLARE @PCount INT
    	DECLARE @vid as int
        SELECT @PCount = Count(A_ID) FROM A
    		WHERE a_Name= @name
    	IF @PCount=1
    	BEGIN
    		UPDATE A 
    		SET @name=A_Name,@description=A_Description
    		WHERE A_ID=1
    	END
    	Begin
    	 Update AC SET C_id=@C_ID,cost=@Cost WHERE AC_ID=1
    	END
    	Return SCOPE_IDENTITY()
    END


You can then use these variables depending on want you are trying to do in the stored procedure. You will also need to remove @C_id declaration as it is already defined in the parameters. Likewise the update AC statement has been changed so the field are updated to the parameter values.
 
Share this answer
 
v2
Comments
Member 13542464 26-Nov-17 19:55pm    
Hi I tried above code . The first time it executed it affected 3 rows, when i tried second time it says "The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead". What could be the issue ?
Thanks in advance!
RossMW 26-Nov-17 20:02pm    
The Return SCOPE_IDENTITY() command returns the identity of an inserted row. Your store procedure is trying to just do updates. As I don't know exactly what you are trying to achieve I can not comment on why it was there in the first place. Try removing it.

Trail and error is your friend if its not production data.....
Member 13542464 26-Nov-17 20:21pm    
Thanks Ross Please see my updated question for more info.
Thanks in advance!
RossMW 26-Nov-17 20:40pm    
What is the relationship of Table C to the other tables? It does not appear to be of any value.
Member 13542464 26-Nov-17 20:49pm    
AC is bridge table between A and C. SO A contains product info. C contains vendor info who sells products and AC act as bridge which include cost of the product (sold by vendors that are in c)
Ross Thanks for your effort and time. Actually I was bit confused, why would SQL INSERT values twice. Now It make sense. Because SQL is UPDATING/INSERTING values in A table then, It will insert produced a_id into AC table. So 2 rows will be affected.
Thanks again for help! I used below code:

<pre lang="SQL">CREATE PROCEDURE Test2 (@name varchar(30),@description varchar(255),@venid int,@cost int)
AS
BEGIN
DECLARE @bid int
INSERT INTO A (A_Name,A_Description) VALUES (@name,@description)
SET @bid =SCOPE_IDENTITY()
INSERT INTO AC(COST,C_ID,A_ID)
VALUES(@cost,@venid,@bid)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900