1
Answer

Stored Procedure to Insert Values in multiple Tables at once

Here is my sql query.I have three tables. they are Floors(FloorId(pk),FloorName),Blocks (FloorId(fk),BlockId(pk),BlockName) ,Rooms(BlockId(fk),RoomId(pk),RoomName) .I want to Insert Values in Floors,Blocks & Rooms Tables at once.
 
 
 
USE [NewDatabase]
GO
/****** Object: StoredProcedure [dbo].[uspinsertion] Script Date: 02/15/2014 12:47:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[uspinsertion]
( @FloorId int,
@FloorName nvarchar(20),
@BlockId int,
@BlockName nvarchar(20),
@RoomId int,
@RoomName nvarchar(20) )
as
begin
set nocount on
DECLARE @RecordID INT,@RecordID1 INT

insert into Floors(FloorId,FloorName)
Values(@FloorId,@FloorName)
SET @RecordID=Scope_Identity()
end

set @RecordID=@FloorId
insert into Blocks (FloorId,BlockId,BlockName)
Values (@RecordID,@BlockId,@BlockName)
set @RecordID=Scope_Identity()
end

Set @RecordID1=@BlockId
Insert Into Rooms(BlockId,RoomId,RoomName)
Values (@RecordID1,@RoomId,@RoomName)
SET @RecordID1=Scope_Identity()
Return

Answers (1)