Calling SQL server procedure to INSERT values for two more columns in SQL server 2014

Question

2.00/5 (1 vote)

See more:

SQL-Server-2014

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 26-Nov-17 12:20pm

Member 13542464

Updated 27-Nov-17 15:03pm

v6

Add a Solution

3 solutions

Solution 1

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.

Posted 26-Nov-17 13:29pm

RossMW

Updated 26-Nov-17 13:31pm

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)

Solution 3

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)

Posted 27-Nov-17 15:03pm

Member 13542464

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RossMW · Accepted Answer · 2017-11-26T15:05:00

Solution 2

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.

Posted 26-Nov-17 15:05pm

RossMW

Updated 26-Nov-17 15:33pm

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)