CODEWITHSUNDEEP
mysqlsqlsql-server-2008
Nida
Nida

Reputation: 1702

Perform insert and update operation in one procedure

I have a table Country in database with the following fields

Now I have to write a procedure which will first check whether @CountryName exists or not. If it already exists it should update that row. If it doesn't exists it should perform insert operation...

Upvotes: 2

Views: 105

Answers (3)

Darshan Faldu
Darshan Faldu

Reputation: 1601

I think below script will help you.

CREATE PROCEDURE ProcedureName
@CountryName nvarchar(100),
@CountryID int

AS
BEGIN

IF EXISTS (SELECT 1 FROM dbo.Country WHERE CountryName = @CountryName)
BEGIN
    --UPDATE
END

ELSE
BEGIN
    --INSERT
END

END

You can also do above code with merge keyword of SQL find reference here

Upvotes: 2

SouravA
SouravA

Reputation: 5243

If it is SQL Server 2005(or higher) version you are using, consider using the MERGEstatement. Documentation for MERGE here.

merge [country] as target
using (select @CountryID, @CountryName) as source(id, name)
on (target.Countryid = source.id)
when matched then
    update set CountryName = @CountryName
when not matched then
    insert (CountryId, CountryName) values (source.id, source.name);

Upvotes: 2

Ugur Altay
Ugur Altay

Reputation: 81

Create Proc [ProcedureName]
@CountryName nvarchar(100)
As
Begin
Declare @Count int
Set @Count = (Select count(CountryId) from Country where CountryName = @CountryName)
if @Count > 0
Begin
-- update 
End
else
begin
-- insert
end

End

Upvotes: 2

Related Questions