Combining Insert/Update to one Procedure

Description

Code

/****** 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

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

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)

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;
}