Insert & update records by single stored procedure

In this blog post we are going to discuss Insert & update records by single stored procedure. So let's discuss about this method.

DataBase Table Name : UserDetail

Column Name	Data Type
UserId	int(primary key)
UserName	nvarchar(max)
FirstName	nvarchar(max)
LastName	nvarchar(max)
Email	nvarchar(max)

Create new Stored Procedure by name sp_InsertUpdateUser

-- =============================================
-- Author:      Shivam Srivastava
-- Description: Insert Update User
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertUpdateUser]
    @UserId INT = 0 ,
    @UserName NVARCHAR(MAX) ,
    @FirstName NVARCHAR(MAX) ,
    @LastName NVARCHAR(MAX) ,
    @Email NVARCHAR(MAX) ,
    @Output INT = 0 OUT
AS 
    BEGIN   
        SET NOCOUNT ON ;      
        IF NOT EXISTS ( SELECT TOP 1
                                UserId
                        FROM    dbo.UsersDetail
                        WHERE   UserID = @UserID ) 
            BEGIN
                INSERT  INTO [dbo].[UsersDetail]
                        ( [UserName] ,
                          [FirstName] ,
                          [LastName] ,
                          [Email]                         
                        )
                VALUES  ( @UserName ,
                          @FirstName ,
                          @LastName ,
                          @Email 
                        )
                SET @Output = SCOPE_IDENTITY()  
            END
        ELSE 
            BEGIN 
                UPDATE  [dbo].[UsersDetail]
                SET     [UserName] = @UserName ,
                        [FirstName] = @FirstName ,
                        [LastName] = @LastName ,
                        [Email] = @Email
                WHERE   UserId = @UserId
                SET @Output = @userId
            END
   
    END

Stored Procedure will return UserId as @Output parameter.