INSERT_UPDATE stored proc not working

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 2005 Forums

Transact-SQL (2005)

INSERT_UPDATE stored proc not working

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2007-01-20 : 10:51:40

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

Kristen
Test

22859 Posts

Posted - 2007-01-20 : 13:11:23

After each UPDATE statement put some sort of Error handler (e.g. RAISERROR) if @@ROWCOUNT <> 1

Put some PRINT statements in to see what is happening, and try calling the SProc from your SQL utils, rather than from your application - e.g.


BEGIN TRANSACTION
DECLARE @outid int
EXEC dbo.INSERT_UPDATE_Header
    @admin_menu_id = 999,
    @admin_submenu_id = 999,
    @header_file = 'xxx',
    @header_alt = 'xxx',
    @header_caption = 'xxx',
    @language_id = 999,
    @header_id = 999,
    @header_detail_id = 999,
    @outid = @outid OUTPUT
-- Possible SELECTs here to review "changed" data
ROLLBACK -- Rollback the changes - allowing them to be "re-made" again

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-21 : 10:57:15

Another thing to check is whether you are accidently passing parameter values as NULL.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources