Basic Update Insert Stored Procedure SQL

Also table should have primary key and identity column(id)

Note: You can pass null values to stored procedure parameters, which columns are null in database table

 /USE [PMS]

GO

/****** Object:  StoredProcedure [dbo].[UpdateOpenVacancy]    Script Date: 29-12-2022 15:29:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[UpdateOpenVacancy]

@id int,

@cadreName VARCHAR(100),

@cadreID INT = null,

@instituteName VARCHAR(100) = null,

@instituteID INT = null ,

@disciplineName VARCHAR(100) = null,

@disciplineID INT = null,

@sanctionedStrength INT = null,

@inPosition INT = null,

@vacancy INT = null,

@openVacancy INT

AS

BEGIN

IF (@id > 0)

UPDATE OpenVacancy

SET 

[CadreName] = ISNULL(@cadreName, CadreName)

      ,[CadreID] = ISNULL(@cadreId, [CadreID])

      ,[InstituteName] = ISNULL(@instituteName,[InstituteName])

      ,[InstituteID] = ISNULL(@instituteID,[InstituteID])

      ,[DisciplineName] = ISNULL(@disciplineName,[DisciplineName])

      ,[DisciplineID] = ISNULL(@disciplineID,[DisciplineID])

      ,[SanctionedStrength] = ISNULL(@sanctionedStrength, [SanctionedStrength])

      ,[InPosition] = ISNULL(@inPosition,[InPosition])

      ,[Vacancy] = ISNULL(@vacancy,[Vacancy])

      ,[OpenVacancy] = ISNULL(@openVacancy,[OpenVacancy])

WHERE ID =@id;

ELSE

INSERT INTO OpenVacancy

           ([CadreName], [CadreID],[InstituteName],[InstituteID],[DisciplineName],[DisciplineID],[SanctionedStrength],[InPosition],[Vacancy]

           ,[OpenVacancy])

     VALUES(@cadreName,@cadreID,@instituteName,@instituteID,@disciplineName,@disciplineID,@sanctionedStrength,@inPosition,@vacancy,@openVacancy)

END