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