Click here to Skip to main content
12,688,944 members (32,351 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

12.3K views
5 bookmarked
Posted

Combining Insert/Update to one Procedure

, 8 Apr 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
DescriptionC...

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








EmpIDEmpNameStatus
1AlbertY
2BobN
3ChristianY
4DavidN
5EdwinY


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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

thatraja
Web Developer
India India
Retired from Q/A section & Technical forums since 2014

More coming soon....

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Question5* Pin
Mas1123-Oct-13 2:23
memberMas1123-Oct-13 2:23 
GeneralRe: <i>I would go for direct DELETE statement, if I have to writ... Pin
Ankur\m/3-Apr-11 19:55
memberAnkur\m/3-Apr-11 19:55 
GeneralRe: Its worth to do on the SQL Server side. The latest SQL Serve... Pin
Venkatesh Mookkan3-Apr-11 19:45
memberVenkatesh Mookkan3-Apr-11 19:45 
GeneralI don't like combining the delete as the delete procs footpr... Pin
Mycroft Holmes4-Apr-11 20:16
memberMycroft Holmes4-Apr-11 20:16 
GeneralRe: Agree Dude. But you can see the Update query(Commented with ... Pin
thatraja4-Apr-11 20:23
mvpthatraja4-Apr-11 20:23 
GeneralI still like the idea of not passing the mode to the Procedu... Pin
Venkatesh Mookkan3-Apr-11 17:40
memberVenkatesh Mookkan3-Apr-11 17:40 
GeneralRe: Well INSERT and UPDATE can be done at the same time using th... Pin
Ankur\m/3-Apr-11 19:41
memberAnkur\m/3-Apr-11 19:41 
GeneralRe: I agree dude. But Already Holmes used the same way(which you... Pin
thatraja4-Apr-11 6:57
mvpthatraja4-Apr-11 6:57 
GeneralReason for my vote of 5 Nice one. Pin
E$w@r3-Apr-11 9:02
memberE$w@r3-Apr-11 9:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170113.4 | Last Updated 8 Apr 2011
Article Copyright 2011 by thatraja
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid