SQL Server 2008: Differentiating between insert and update operations in a stored procedure

Data and Databases
1

I’m having trouble with a stored procedure in SQL Server 2008. It’s supposed to return different values for insert and update operations, but it’s not working as expected. Here’s what I want:

  • Return 1 for inserts
  • Return 2 for updates

The procedure inserts and updates correctly, but always returns 1. Here’s a simplified version of my code:

CREATE PROCEDURE [dbo].[SaveEmployeeInfo] 
    @empID int,
    @name varchar(100),
    @dept varchar(50)
AS
BEGIN
    DECLARE @Result int;

    IF NOT EXISTS(SELECT 1 FROM staff WHERE empID = @empID)
    BEGIN
        INSERT INTO staff (empID, name, dept) 
        VALUES (@empID, @name, @dept)
        SET @Result = 1;
    END
    ELSE
    BEGIN
        UPDATE staff
        SET name = @name, dept = @dept
        WHERE empID = @empID
        SET @Result = 2;
    END

    RETURN @Result;
END

What am I doing wrong? How can I make it return the correct value for each operation?

2

hey there! have you tried using SELECT instead of RETURN? i ran into a similar issue once. maybe something like this could work:

IF NOT EXISTS(SELECT 1 FROM staff WHERE empID = @empID)
BEGIN
    INSERT INTO staff (empID, name, dept) 
    VALUES (@empID, @name, @dept)
    SELECT 1 AS Result;
END
ELSE
BEGIN
    UPDATE staff
    SET name = @name, dept = @dept
    WHERE empID = @empID
    SELECT 2 AS Result;
END

what do you think? curious to hear if this helps!

3

I’ve encountered this issue before, and the problem lies with how SQL Server handles the RETURN statement in stored procedures. It’s not designed to return data in the way you’re trying to use it. Instead, I’d recommend using OUTPUT parameters or SELECT statements to return your result.

Here’s a modification that should work:

CREATE PROCEDURE [dbo].[SaveEmployeeInfo] 
    @empID int,
    @name varchar(100),
    @dept varchar(50),
    @Result int OUTPUT
AS
BEGIN
    IF NOT EXISTS(SELECT 1 FROM staff WHERE empID = @empID)
    BEGIN
        INSERT INTO staff (empID, name, dept) 
        VALUES (@empID, @name, @dept)
        SET @Result = 1
    END
    ELSE
    BEGIN
        UPDATE staff
        SET name = @name, dept = @dept
        WHERE empID = @empID
        SET @Result = 2
    END
END

You can then call this procedure and retrieve the result using an OUTPUT parameter. This approach is more reliable and aligns better with SQL Server’s intended use of stored procedures.

4

yo Alex_Dynamo, i think i see the issue. RETURN doesn’t work like you expect in stored procs. try using SELECT instead:

IF NOT EXISTS(SELECT 1 FROM staff WHERE empID = @empID)
BEGIN
    INSERT INTO staff (empID, name, dept) 
    VALUES (@empID, @name, @dept)
    SELECT 1 AS Result;
END
ELSE
BEGIN
    UPDATE staff
    SET name = @name, dept = @dept
    WHERE empID = @empID
    SELECT 2 AS Result;
END

this should give you what you want. lemme know if it helps!