'INSERT' stored procedure with validation

Question

I'm trying to create a stored procedure where I'm inserting a new office into the OFFICE table I have in my database.

I want to first check whether the office I'm trying to create already exists or not.

Here is some code from where I've gotten so far, but I'm not able to quite get it right. I would greatly appreciate some input.

CREATE PROCEDURE stored_proc_new_office
AS 
BEGIN    
    DECLARE @office_id int
    SELECT @office_id = (SELECT office_id FROM inserted)

    IF NOT EXISTS (SELECT 1 FROM OFFICE WHERE office_id = @office_id)
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'Office already exists.'
    END
END

Answer 1

Here is a bare bones example of how you can use a stored procedure to insert a new record with a check to ensure it doesn't already exist.

create procedure dbo.AddNewOffice
(
    @Name nvarchar(128)
    -- ... add parameters for other office details
    , @NewId int out
)
as 
begin
    set nocount on;

    insert into dbo.Office([Name]) -- ... add additional columns
    select @Name -- ... add additional parameters to match the columns above
    where not exists (select 1 from dbo.Office where [Name] = @Name); -- ... add any additional conditions for testing for uniqueness

    -- If nothing inserted return an error code for the calling app to use to display something meaningful to the user
    if @@rowcount = 0 return 99;

    -- return the new id to the calling app.
    set @NewId = scope_identity();

    return 0;
end