Store Procedure IF Else Newbie Question

harpua · November 1st, 2004, 06:37 PM

Can somebody please help me out here. I am trying to write some code that will determine whether to INSERT or UPDATE depending on if the @StoryID has a value or not. I have tried
@StoryID = NULL , IS NULL = "" just about everything I can think of. What is happening is that it always INSERTS, it will not update. Any help is greatly appreciated.

CREATE PROCEDURE sp_ModifyStories
(
@StoryID int,
@StoryDate datetime,
@StoryNo varchar(100),
@StoryTitle varchar(200),
@ModifiedID int OUTPUT
)
AS
IF @StoryID < 1
    BEGIN
    INSERT INTO Stories
    (Story_Date, Story_No, Story_Title)
    VALUES
    (@StoryDate, @StoryNo, @StoryTitle)

    SELECT @ModifiedID = @@IDENTITY
    END
ELSE
    BEGIN
    UPDATE Stories
    SET
        Story_Date = @StoryDate,
        Story_No = @StoryNo,
        Story_Title = @StoryTitle,
    WHERE Story_ID = @StoryID
    END
GO

Thanks
MIke

happygv · November 1st, 2004, 08:31 PM

Try this...

Code:

......
AS
BEGIN TRAN
IF (select count(*) from Stories where Story_ID = @StoryID)=0
    BEGIN
    INSERT INTO Stories
    (Story_Date, Story_No, Story_Title) 
    VALUES
    (@StoryDate, @StoryNo, @StoryTitle)
    SELECT @ModifiedID = @@IDENTITY
    END
ELSE
    BEGIN
    UPDATE Stories
    SET
        Story_Date = @StoryDate,
        Story_No = @StoryNo,
        Story_Title = @StoryTitle,
    WHERE Story_ID = @StoryID
    END
IF @@ERROR <> 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN
Select Result=@@ERROR
Go

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection