If Row Exists, Update, Else Insert in Asp .net
fields : StudentID, FirstName, LastName and Status
CREATE PROCEDURE ExistsUpdateInsert(
@StudentID int,
@FirstName [nvarchar](50),
@LastName [nvarchar](50),
@Status [nvarchar](50)
)
AS
BEGIN
IF EXISTS (SELECT StudentID FROM StudentTable WHERE StudentID =@StudentID)
UPDATE StudentTable set StudentID=@StudentID WHERE Status='Active'
ELSE
INSERT INTO StudentTable (StudentID, FirstName, LastName)
VALUES (@StudentID, @FirstName, @LastName)
END
END
please help code
Loading
Sunny SharmaPosted Oct 14, 2013, 7:44 AM
Here you go, use the code snippet below:
----------------------------------------------
CREATE PROCEDURE ExistsUpdateInsert(
@StudentID int,
@FirstName [nvarchar](50),
@LastName [nvarchar](50),
@Status [nvarchar](50)
)
AS
BEGIN
IF EXISTS (SELECT StudentID FROM StudentTable WHERE StudentID =@StudentID)
UPDATE StudentTable SET FirstName=@FirstName, LastName=@LastName, Status=@Status WHERE Status='Active' AND StudentID=@StudentID;
ELSE
INSERT INTO StudentTable (StudentID, FirstName, LastName)
VALUES (@StudentID, @FirstName, @LastName);
END
--------------------------------------------------------
Happy Coding :)
Sandeep Singh ShekhawatPosted Oct 14, 2013, 11:42 AM
Hi Sanjay,
The following stored procedure is okay for update and insert but I want to share few point.
1. You should not update StudentId because it is using to check for already existing record in table.
2. Status is reserved keyword in sql server so you can't directly insert any inserting if you insert the it will be null and your data will not update on basic of it. So write code for insert update by stored procedure like
EXEC ExistsUpdateInsert 1,'Sandeep ','Singh',N'Active'
3. You are using StudentId for check exising so used it into update query the condition.
4. You should use other fields in update condition set fields instead of StudentId.
5. You should insert [Status] field in insert statement otherwise it will be null and can't perform update operation.
CREATE PROCEDURE ExistsUpdateInsert
(
@StudentID int,
@FirstName [nvarchar](50),
@LastName [nvarchar](50),
@Status [nvarchar](50)
)
AS
BEGIN
IF EXISTS (SELECT StudentID FROM StudentTable WHERE StudentID = @StudentID)
BEGIN
UPDATE StudentTable set FirstName = @FirstName,LastName = @LastName
WHERE [Status] = 'Active'
END
ELSE
BEGIN
INSERT INTO StudentTable (StudentID, FirstName, LastName,[Status] )
VALUES (@StudentID, @FirstName, @LastName,@Status )
END
END
Jeetendra GundPosted Oct 14, 2013, 7:43 AM
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
CREATE PROCEDURE ExistsUpdateInsert(
@StudentID int,
@FirstName [nvarchar](50),
@LastName [nvarchar](50),
@Status [nvarchar](50)
)
AS
BEGIN
IF EXISTS (SELECT StudentID FROM StudentTable WHERE StudentID=@StudentID)
UPDATE StudentTable
SET StudentID=@StudentID WHERE Status='Active'
ELSE
INSERT INTO StudentTable (StudentID, FirstName, LastName)
VALUES (@StudentID, @FirstName, @LastName)
END
END
Hi try this ...
Thanks..
Sanjay KumarPosted Oct 14, 2013, 7:33 AM
Jeetendra GundPosted Oct 14, 2013, 7:20 AM