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
