insert in 2 tables simultaneously

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

insert in 2 tables simultaneously

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2006-09-09 : 10:56:10

Dear All,

I am not an expert on T-SQL and I am trying out a small project to learn.

I have encountered a problem, whereby I have 2 tables, 1 containing the header (header_id, header_file, admin_menu_id and admin_submenu_id) and then I have another table called header_details, where I am storing the language details for this header, with the fields being (header_details_id, fk_header_id, header_alt, header_caption and fk_language_id)

Now I want to create a stored proc, first to insert the header and then the header details. I also want that if the header or header details already exist, I just do an update on these tables.

I tried the following code but its not working:-

ALTER Procedure [dbo].[INSERT_Header]
(
@admin_menu_id int,
@admin_submenu_id int,
@header_file varchar(150),
@header_alt varchar(150),
@header_caption varchar(200),
@language_id int
@outID int OUTPUT
)
AS
BEGIN

-- First do a select on the header table to see if this header already exists

DECLARE @count integer = 0

SELECT COUNT(*) as @count
FROM headers
WHERE [fk_admin_menu_id] = @admin_menu_id
AND [fk_admin_submenu_id] = @admin_submenu_id

--if it exists, then update this header
IF @count > 0
BEGIN
UPDATE [headers]
SET [header_file] = @header_file

WHERE [fk_admin_menu_id] = @admin_menu_id
[fk_admin_submenu_id] = @admin_submenu_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@header_file
,@admin_menu_id
,@admin_submenu_id)
-- Get the Inserted Header ID --
SET @outID = SCOPE_IDENTITY()
END

-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @count_details integer = 0

SELECT COUNT(*) as @count_details
FROM header_detail
WHERE [fk_admin_menu_id] = @admin_menu_id
AND [fk_admin_submenu_id] = @admin_submenu_id
AND [fk_language_id] = @language_id

--if it exists, then update this header
IF @count_details > 0
BEGIN
UPDATE [header_detail]
SET [header_alt] = @header_file
,[header_caption] = @header_caption

WHERE [fk_header_id] = @header_id
[fk_language_id] = @language_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@@out_ID
,@header_alt
,@header_caption
,@language_id)
END

END

Can you help me out please?

Thanks a lot for your help and time

Johann

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-09 : 22:46:24

I didn't check your entire stored proc, but I know that this part is wrong:

SELECT COUNT(*) as @count
FROM headers

You need to do this instead:

SELECT @count = COUNT(*)
FROM headers

Same here:

SELECT @count_details = COUNT(*)
FROM header_detail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-10 : 03:25:21

There are a few things wrong as i gathered.

1. @language_id int
@outID int OUTPUT

should have a comma here

@language_id int,
@outID int OUTPUT

2. DECLARE @count integer = 0
should be
DECLARE @count int
set @count = 0

3. correct code

INSERT INTO [headers]
([header_file],
[fk_admin_menu_id],
[fk_admin_submenu_id])
VALUES
(@header_file,
@admin_menu_id,
@admin_submenu_id)

set @count_details = (SELECT COUNT(*) FROM header_detail)

see nosepickers, corrections above also

Afrika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources