Stored Procedure Help

Dev Shed Forums Sponsor:

October 17th, 2004, 03:01 AM

travis801

Registered User

Join Date: Jun 2004

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Stored Procedure Help

I have two tables that I would like to update with one stored procedure:

Code:

USE GNEITING_HOBS
GO
CREATE PROCEDURE sp_InsertRooms
	@HotelID		SMALLINT,	--RoomTypes
	@RoomType		varchar(15),	--RoomTypes
	@RackRoomRate		SMALLMONEY,	--RoomTypes
	--@RoomTypeID		int 	Output	--RoomTypes
	@RoomtypeID		smallint,
	@RoomNumber		smallint,
	@RoomDescription	text,
	@Smoking		char,
	@BedConfiguration	char

AS
INSERT INTO RoomTypes
VALUES(
	@HotelID,	--RoomTypes
	@RoomType,	--RoomTypes
	@RackRoomRate	--RoomTypes
)

INSERT INTO RoomDetails
VALUES(
	@RoomtypeID,		
	@RoomNumber,
	@RoomDescription,
	@Smoking,
	@BedConfiguration
)

The following works, however, I would like to a Primary key from the new value inserted into the Roomtypes table to populate the foreign key in the RoomDetails table. I think I need to use @@IDENTITY some how, but am not sure the syntax?

Thanks for any help.

November 6th, 2004, 12:07 PM

rainbow11

Contributing User

Join Date: May 2004

Posts: 86

Time spent in forums: 2 h 15 m 22 sec
Reputation Power: 5

Hi all,
Is someone has answer for this.
I have to do the same thing too.
Thanks in advance
Rainbow11

November 6th, 2004, 01:33 PM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Code:

CREATE PROCEDURE sp_InsertRooms
	@HotelID		SMALLINT,	--RoomTypes
	@RoomType		varchar(15),	--RoomTypes
	@RackRoomRate		SMALLMONEY,	--RoomTypes
	@RoomTypeID		int 	Output,	--RoomTypes
	@RoomNumber		smallint,
	@RoomDescription	text,
	@Smoking		char,
	@BedConfiguration	char

AS
set nocount on
INSERT INTO RoomTypes(hotelid,roomtype,rackroomrate)
VALUES(
	@HotelID,	--RoomTypes
	@RoomType,	--RoomTypes
	@RackRoomRate	--RoomTypes
)
set @RoomTypeID		= scope_identity()
INSERT INTO RoomDetails
VALUES(
	@RoomtypeID,		
	@RoomNumber,
	@RoomDescription,
	@Smoking,
	@BedConfiguration
)

Last edited by swampBoogie : November 6th, 2004 at 01:36 PM.

November 6th, 2004, 10:42 PM

rainbow11

Contributing User

Join Date: May 2004

Posts: 86

Time spent in forums: 2 h 15 m 22 sec
Reputation Power: 5

thanks swampBoogie for answer.
it works for me.
thanks again for your time and reply

rainbow11

Viewing: Dev Shed Forums > Databases > MS SQL Development > Stored Procedure Help

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump