Combining Insert/Update to one Procedure

Description

Combining Insert/Update/Delete to one Procedure in SQL Server.

Code

Let's take Employee Table for example. Table

/****** Object:  Table [dbo].[tblEmp]    Script Date: 04/03/2011 16:50:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tblEmp](
	[EmpID] [int] NOT NULL,
	[EmpName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblEmp_EmpName]  DEFAULT (''),
	[Status] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblEmp_Status]  DEFAULT (''),
 CONSTRAINT [PK_tblEmp] PRIMARY KEY CLUSTERED 
(
	[EmpID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Table with Sample Values

EmpID	EmpName	Status
1	Albert	Y
2	Bob	N
3	Christian	Y
4	David	N
5	Edwin	Y

Stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Usp_EmpOperations]
(
	@EmpID INT,
	@EmpName VARCHAR(50),
	@Status CHAR(1),
	@Mode VARCHAR(10),
	@Result INT OUT
)
AS
	IF @Mode='INSERT'
		BEGIN

			INSERT INTO tblEmp(EmpID,EmpName,Status) VALUES(@EmpID,@EmpName,@Status)

			SET @Result=1
		END
	ELSE IF @Mode='UPDATE'
		BEGIN

			UPDATE tblEmp SET EmpName=@EmpName, Status=@Status
			WHERE EmpID=@EmpID

			SET @Result=1

		END
	ELSE IF @Mode='DELETE'
		BEGIN

			DELETE tblEmp WHERE EmpID=@EmpID
			--UPDATE tblEmp SET Status='D' WHERE EmpID=@EmpID -- If you don't want to delete the record then set the status with different value (Ex. D for Deleted)

			SET @Result=1

		END
	RETURN @Result
	IF @@ERROR<>0
		SET @Result=@@ERROR

Execution

exec Usp_EmpOperations 6,'Fahran','N','INSERT',0 -- Insert New Emp Fahran
exec Usp_EmpOperations 5,'Eswar','N','UPDATE',0  -- Update the EmpName for ID 5
exec Usp_EmpOperations 4,'David','N','DELETE',0  -- Delete the Emp David (ID - 4)

C#.NET code

    public int EmpOperations()
    {   int Result = 0;
        string conString = "server=[servername]; uid=[uid]; password=[password]; database=[databasename];"; // Use connection string from your web.config
        using (SqlConnection conn = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("Usp_EmpOperations"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@EmpID", "6"));
                cmd.Parameters.Add(new SqlParameter("@EmpName", "Fahran"));
                cmd.Parameters.Add(new SqlParameter("@Status", "N"));
                cmd.Parameters.Add(new SqlParameter("@Mode", "INSERT"));

                SqlParameter param = new SqlParameter("@Result", 0);
                param.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(param);

                conn.Open();
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                Result = Int32.Parse(cmd.Parameters["@Result"].Value.ToString());
                conn.Close();
            }
        }
        return Result;
    }