3 Replies - 1986 Views - Last Post: 15 January 2012 - 06:54 AM

#1 whatquestions  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 15-September 11

how to write stored procedure from 2tables?

Posted 14 January 2012 - 02:49 AM

+------------+     +-----------------+     
| record     |     | record_items    |     
+------------+     +-----------------+    
|RecordID(PK)|>--->| RecordID (FK)   |  
|PatientID   |     | ItemID          |     
|Precription |     | Amount          |
|VisitDate   |     +-----------------+
+------------+                                  
                

i have 2 tables as shows, how to write a stored procedure to store data into two tables?
i only know store procedure for 1 table
ALTER PROCEDURE [dbo].[InsertStaff]
	-- Add the parameters for the stored procedure here
	@StaffName VARCHAR(50),
	@Gender VARCHAR(50),
	@ICnum VARCHAR(50),
	@DOB DATETIME,
	@Age NUMERIC(18,0),
	@TypeOfJob VARCHAR(50),
	@Contact INT,
	@Cellphone INT,
	@Email VARCHAR(50),
	@RegisterDate DATETIME,
	@Salary MONEY,
	@SAddress VARCHAR(50),
	@SCity VARCHAR(50),
	@SZipCode INT

	
AS
	INSERT INTO staff(StaffName,Gender,ICnum,DOB,Age,TypeOfJob,Contact,Cellphone,Email,RegisterDate,Salary,SAddress,SCity,SZipCode)
	VALUES(@StaffName,@Gender,@ICnum,@DOB,@Age,@TypeOfJob,@Contact,@Cellphone,@Email,@RegisterDate,@Salary,@SAddress,@SCity,@SZipCode)

    
RETURN

This post has been edited by whatquestions: 14 January 2012 - 02:50 AM


Is This A Good Question/Topic? 0
  • +

Replies To: how to write stored procedure from 2tables?

#2 Vishu Sukhdev  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 150
  • Joined: 19-February 10

Re: how to write stored procedure from 2tables?

Posted 14 January 2012 - 04:38 AM

Use Second Insert Statement below the first one.
Was This Post Helpful? 0
  • +
  • -

#3 whatquestions  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 15-September 11

Re: how to write stored procedure from 2tables?

Posted 14 January 2012 - 05:00 AM

just like that??
the "RecordID" will also auto increment with same id??
Was This Post Helpful? 0
  • +
  • -

#4 whatquestions  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 15-September 11

Re: how to write stored procedure from 2tables?

Posted 15 January 2012 - 06:54 AM

ALTER PROCEDURE [dbo].[InsertPatientRecord]
	-- Add the parameters for the stored procedure here
	@PatientID INT,
	@Prescription VARCHAR(50),
	@VisitDate DATETIME,
	@ItemID INT,
	@Amount INT
	
	
AS
begin
	[color="#FF0000"]INSERT INTO record(PatientID,Prescription,VisitDate)
	VALUES(@PatientID,@Prescription,@VisitDate)[/color]
end
begin
	INSERT INTO record_item(ItemID,Amount)
	VALUES(@ItemID,@Amount)
end
    
RETURN


why after input data from system, only "record"table inserted, record_item remain empty???
and "RecordID" from table "record_item" is foreign key from "record" table. How to make sure both "RecordID" will increase together with same id??i have put the "RecordID" auto increment from "record"
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1