Example of SQL server using stored procedure to insert and update data

Time:2021-7-1

realization

1) If you have the same data, you can return it directly (return value: 0);

2) Update the data with the same primary key but different data (return value: 2);

3) No data, insert data processing (return value: 1).

Create stored procedure

Create proc Insert_Update 

@Id varchar(20), 

@Name varchar(20), 

@Telephone varchar(20), 

@Address varchar(20), 

@Job varchar(20), 

@returnValue int output 

as 

declare 

@tmpName varchar(20), 

@tmpTelephone varchar(20),


@tmpJob varchar(20),

@tmpAddress varchar(20)

if exists(select * from dbo.DemoData where [email protected]) 

begin 

select @tmpName=Name,@tmpTelephone=Telephone,@tmpAddress=Address,@tmpJob=Job from dbo.DemoData where [email protected] 

if ((@[email protected]) and (@[email protected]) and (@[email protected])and (@[email protected])) 

begin 

Set @ returnValue = 0 -- with the same data, return the value directly 

end 

else 

begin 

update dbo.DemoData set [email protected],[email protected],[email protected],[email protected] where [email protected] 

Set @ returnValue = 2 -- data with the same primary key is updated 

end 

end 

else 

begin 

insert into dbo.DemoData values(@Id,@Name,@Telephone,@Address,@Job) 

Set @ returnValue = 1 -- no same data, insert it 

end

[implementation method]

declare @returnValue int 
exec Insert_ Update '15','hugh15 ','3823345','chang'an Street ','vice minister' ,@returnValue  output 
select @returnValue

The return value is 0. The same value already exists

Return value 1, insert successfully

The return value is 2. The update is successful