SQL Server inserts update data sample with stored procedure implementation _mssql

Realize

1) have the same data, return directly (return value: 0);

2 has the same primary key, but the data different data, carries on the update processing (returns the value: 2);

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

"Create a stored procedure"

Create proc insert_update 

@Id varchar, 

@Name varchar, 

@Telephone varchar, 

@Address varchar, 

@Job varchar, 

@returnValue int output as 

declare 

@tmpName varchar ( 

D) Tmptelephone varchar,


@tmpJob varchar,

@tmpAddress varchar

if exists (SELECT * FROM dbo. Demodata where id= @Id) 

begin 

Select @tmpName =name, @tmpTelephone =telephone, @tmpAddress =address, @tmpJob = Job from dbo. Demodata where id= @Id 

if (@tmpName = @Name) and (@tmpTelephone = @Telephone) and (@tmpAddress = @Address) and (@tmpJob = @Job)) 

begin 

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

end 

else 

begin 

UPDATE dbo. Demodata set name= @Name, telephone= @Telephone, address= @Address, job= @Job where id= @Id 

Set @returnValue = 2- With the same data as the primary key, the update processing end 

else is 

begin insert INTO 

dbo. Demodata values (@Id, @Name, @Telephone, @Address, @Job) 

Set @returnValue = 1-No identical data, insert processing end 

"How to execute"

DECLARE @returnValue int 
exec insert_update ', ' hugh15 ', ' 3823345 ', ' Changan Street ', ' Deputy minister ', @returnValue output 
SELECT @ ReturnValue

Returns a value of 0, which already exists the same

Return value 1, insert succeeded

Return value 2, update succeeded