2

I have a table Country in database with the following fields

  • CountryId
  • CountryName

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...

Nida
  • 1,672
  • 3
  • 35
  • 68
  • 2
    doublicate to : http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server – Chaka Jul 29 '15 at 06:23

3 Answers3

2

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

Community
  • 1
  • 1
Darshan Faldu
  • 1,471
  • 2
  • 15
  • 32
2
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
Ugur Altay
  • 81
  • 3
2

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);
SouravA
  • 5,147
  • 2
  • 24
  • 49