January 20, 2007 at 9:01 am
Dear All,
I have an Insert_Update stored proc however its not working. The Insert is working fine, however the Update is not updating my record. Here is my stored proc:-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*
*********************************************************************************************************************
******This stored procedure inserts a header according to its ID.
******Created :- Johann Montfort
******Date:- 20/10/2006
*********************************************************************************************************************
*/
ALTER Procedure [dbo].[INSERT_UPDATE_Header]
(
@admin_menu_id int,
@admin_submenu_id int,
@header_file varchar(150),
@header_alt varchar(150),
@header_caption varchar(200),
@language_id int,
@header_id int,
@header_detail_id int,
@outid int OUTPUT
)
AS
BEGIN
IF @header_id = -1
BEGIN
-- Insert the into headers --
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 @header_id = SCOPE_IDENTITY()
IF @header_id IS NOT NULL
BEGIN
-- Insert the into headers --
INSERT INTO [header_detail]
(
[fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id]
)
VALUES
(
@header_id
,@header_alt
,@header_caption
,@language_id
)
-- Get the Inserted header_detail --
SET @outid = SCOPE_IDENTITY()
END
ELSE
-- Update header in header and header_detail table--
BEGIN
UPDATE [headers]
SET [header_file]= @header_file
,[fk_admin_menu_id] = @admin_menu_id
,[fk_admin_submenu_id] = @admin_submenu_id
WHERE [header_id] = @header_id
UPDATE [header_detail]
SET [header_alt]= @header_alt
,[header_caption] = @header_caption
,[fk_language_id] = @language_id
WHERE [fk_header_id] = @header_id
AND [fk_language_id] = @language_id
END
END
END
I am passing 20 as the header_id, so its supposed to go to the Update.
Another thing, I tried to debug it through vs2005, (right click + Step Into Stored Proc) but I could not go through it
Any ideas please?
Thanks for your help and time
Johann
January 20, 2007 at 12:02 pm
You misplace the BEGIN 's END for the INSERT CASE. It should be before ELSE
cheers
January 23, 2007 at 12:57 pm
Hi ijaz
I tried it like this:-
/*
*********************************************************************************************************************
******This stored procedure inserts a header according to its ID.
******Created :- Johann Montfort
******Date:- 20/10/2006
*********************************************************************************************************************
*/
ALTER Procedure [dbo].[INSERT_UPDATE_Header]
(
@admin_menu_id int,
@admin_submenu_id int,
@header_file varchar(150),
@header_alt varchar(150),
@header_caption varchar(200),
@language_id int,
@header_id int,
@header_detail_id int,
@outid int OUTPUT
)
AS
BEGIN
IF @header_id = -1
BEGIN
-- Insert the into headers --
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 @header_id = SCOPE_IDENTITY()
IF @header_id IS NOT NULL
BEGIN
-- Insert the into headers --
INSERT INTO [header_detail]
(
[fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id]
)
VALUES
(
@header_id
,@header_alt
,@header_caption
,@language_id
)
-- Get the Inserted header_detail --
SET @outid = SCOPE_IDENTITY()
END
ELSE
-- Update header in header and header_detail table--
BEGIN
PRINT 'I am here'
UPDATE [headers]
SET [header_file]= @header_file
,[fk_admin_menu_id] = @admin_menu_id
,[fk_admin_submenu_id] = @admin_submenu_id
WHERE [header_id] = @header_id
UPDATE [header_detail]
SET [header_alt]= @header_alt
,[header_caption] = @header_caption
,[fk_language_id] = @language_id
WHERE [fk_header_id] = @header_id
AND [fk_language_id] = @language_id
END
END
END
Still not working though
January 23, 2007 at 2:26 pm
You will save yourself a career-full of problems if you indent your code:
IF @header_id = -1
BEGIN
-- Insert the into headers --
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 @header_id = SCOPE_IDENTITY()
IF @header_id IS NOT NULL
BEGIN
-- Insert the into headers --
INSERT INTO [header_detail]
(
[fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id]
)
VALUES
(
@header_id
,@header_alt
,@header_caption
,@language_id
)
-- Get the Inserted header_detail --
SET @outid = SCOPE_IDENTITY()
END
ELSE
-- Update header in header and header_detail table--
BEGIN
PRINT 'I am here'
-- WHOOOOPS!! I won't ever get here if I pass @header_id as 20
UPDATE [headers]
January 25, 2007 at 11:38 am
As mentioned above, indentation is a wonderful thing. And your code has a misplaced END. Try this
SET ansi_nulls ON
SET quoted_identifier ON
GO
/* *********************************************************************************************************************
******This stored procedure inserts a header according to its ID.
******Created :- Johann Montfort
******Date:- 20/10/2006
********************************************************************************************************************* */
ALTER PROCEDURE [dbo].[insert_update_header](
@admin_menu_id INT,
@admin_submenu_id INT,
@header_file VARCHAR(150),
@header_alt VARCHAR(150),
@header_caption VARCHAR(200),
@language_id INT,
@header_id INT,
@header_detail_id INT,
@outid INT OUTPUT)
AS
BEGIN
IF @header_id = -1
BEGIN
-- Insert the into headers --
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 @header_id = Scope_identity()
IF @header_id IS NOT NULL
BEGIN
-- Insert the into headers --
INSERT INTO [header_detail]
([fk_header_id],
[header_alt],
[header_caption],
[fk_language_id])
VALUES (@header_id,
@header_alt,
@header_caption,
@language_id)
-- Get the Inserted header_detail --
SET @outid = Scope_identity()
END
END
ELSE
-- Update header in header and header_detail table--
BEGIN
UPDATE [headers]
SET [header_file] = @header_file,
[fk_admin_menu_id] = @admin_menu_id,
[fk_admin_submenu_id] = @admin_submenu_id
WHERE [header_id] = @header_id
UPDATE [header_detail]
SET [header_alt] = @header_alt,
[header_caption] = @header_caption,
[fk_language_id] = @language_id
WHERE [fk_header_id] = @header_id
AND [fk_language_id] = @language_id
END
END
January 28, 2007 at 7:07 am
Thanks Guys
It works
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply