insert simultaneously in 2 tables

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_idint,
@.admin_submenu_idint,
@.header_filevarchar(150),
@.header_altvarchar(150),
@.header_captionvarchar(200),
@.language_idint
@.outIDint 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

Johannhave you done a syntax check... your T-SQL is full of errors

1
DECLARE @.count integer = 0 is not valid, you cannot assign a value on a declaration

2
SELECT COUNT(*) as @.count
FROM headers
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id
does not assign a value to @.count, to do do it you must write as follows:
SELECT @.count=COUNT(*)
FROM headers

.3

WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id
ELSE
is also invalid you must put END berfore else and maybe an AND on the where clause

Are you sure you've tried the statement in query analyzer or management studio ?

|||

Hi,

you should use If Exists construct instead o using Count(*) method as

If Exists (SELECT fk_admin_menu_id FROM headers
WHERE [fk_admin_menu_id] = @.admin_menu_id
AND [fk_admin_submenu_id] = @.admin_submenu_id
)

BEGIN

UPDATE [headers]
SET [header_file] = @.header_file
WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id

END
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

IF EXISTS (SELECT fk_admin_menu_id 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
)

BEGIN

UPDATE [header_detail]
SET [header_alt] = @.header_file
,[header_caption] = @.header_caption
WHERE [fk_header_id] = @.header_id
[fk_language_id] = @.language_id

END

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