Here is my sql query.I have four tables. they are
Floors(FloorId(pk),FloorName),
Blocks(FloorId(fk),BlockId(pk),BlockName)
Rooms(BlockId(fk),RoomId(pk),RoomName)
Seats(RoomId(fk),
SeatId(pk),SeatName)
.I want to Insert Values in Floors,Blocks, Rooms,Seats Tables at once.
*************************************************************************************************
USE [NewDatabase]
GO
/****** Object: StoredProcedure [dbo].[uspinsertion] Script Date:
02/18/2014 20:52:56 ******/
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),
@SeatId int,
@SeatName nvarchar(20)
)
as
begin
set nocount on
DECLARE @RecordID INT
Declare @RecordID1 int
Declare @RecordID2 int
insert into Floors(FloorId,FloorName)
Values(@FloorId,@FloorName)
SET @RecordID=Scope_Identity()
insert into Blocks (FloorId,BlockId,BlockName)
Values (@RecordID,@BlockId,@BlockName)
set @RecordID1=Scope_Identity()
insert into Rooms (BlockId,RoomId,RoomName)
Values (@RecordID1,@RoomId,@RoomName)
set @RecordID2=Scope_Identity()
insert into Seats (RoomId,SeatId,SeatName)
Values (@RecordID2,@SeatId,@SeatName)
End
Return
What is the problem in this sql query.Fail to fix it.Need help.